Is there a way to match any decimal number with T-SQL 'LIKE' expression?

Question

For example, something like:

SELECT * FROM myTable WHERE myColumn LIKE '[0-9]n.[0-9]n'

Of course I know the syntax above will not work, but I m trying to explain: n digits, followed by a period, followed by n digits. Thanks

Answer 1

You could use following patters for unsigned decimal values:

SELECT  *, 
        CASE 
            WHEN (x.Col1 LIKE '%[0-9]%.%[0-9]%' /*OR x.Col1 LIKE '.%[0-9]%' OR x.Col1 LIKE '%[0-9]%.' OR x.Col1 LIKE '%[0-9]%'*/) 
            AND x.Col1 NOT LIKE '%[^0-9.]%'  
            THEN 1 ELSE 0 
        END AS IsDecimal,
        ISNUMERIC(x.Col1) AS [IsNumeric]
FROM (
    SELECT '1.23' UNION ALL SELECT '.23' UNION ALL SELECT '1.'  UNION ALL SELECT '123'  UNION ALL 
    SELECT NULL UNION ALL SELECT '' UNION ALL SELECT '1A.23'  UNION ALL SELECT '1.2A3'  UNION ALL SELECT 'A.B'  UNION ALL 
    SELECT '.' UNION ALL SELECT '$' 
) x(Col1)

/*
Col1  IsDecimal   IsNumeric
----- ----------- -----------
1.23  1           1
.23   0           1
1.    0           1
123   0           1
NULL  0           0
      0           0
1A.23 0           0
1.2A3 0           0
A.B   0           0
.     0           1
$     0           1
*/

If you uncomment /*OR x.Col1 LIKE '.%[0-9]%' OR x.Col1 LIKE '%[0-9]%.' OR x.Col1 LIKE '%[0-9]%'*/ then .23 , 1. and 123 will be considered (also) valid decimal values.

Answer 2

Do you need exactly n or just anything that will cast to decimal

select cast(varDec as Decimal(p,s)) 
where varDec not like '%[^.1-9]%' 
and varDec not like '%.%.%'
and len(varDec) > 0 
and varDec <> '.'