CODEWITHSUNDEEP
sqlsql-servernumbers
Thunder
Thunder

Reputation: 10986

sql like operator to get the numbers only

This is I think a simple problem but not getting the solution yet. I would like to get the valid numbers only from a column as explained here.

Lets say we have a varchar column with following values

ABC
Italy
Apple
234.62
2:234:43:22
France
6435.23
2
Lions

Here the problem is to select numbers only

select * from tbl where answer like '%[0-9]%' would have done it but it returns 

    234.62
    2:234:43:22
    6435.23
    2

Here, obviously, 2:234:43:22 is not desired as it is not valid number.

The desired result is

        234.62
        6435.23
        2

Is there a way to do this?

Upvotes: 44

Views: 243730

Answers (6)

Clement
Clement

Reputation: 11

SELECT * FROM @Table
WHERE Col NOT LIKE '%[^0-9.]%' AND ISNUMERIC(Col)=1 AND Col!='.'

Explanation: NOT LIKE '%[^0-9.]%' means "no string with a character that is not a figure or a dot" ISNUMERIC(Col)=1 means "the SQL function to identify a numeric must return OK" (that can avoid strings with multiple dots) Col!='.' means "Col is not a dot" (a dot is considered as numeric by above function)

Next stop: the Regex gurus that will do this in a single line ;)

a+,=) -=Clement=-

Upvotes: 0

Ruud
Ruud

Reputation: 71

what might get you where you want in plain SQL92:

select * from tbl where lower(answer) = upper(answer)

or, if you also want to be robust for leading/trailing spaces:

select * from tbl where lower(answer) = trim(upper(answer))

Upvotes: 0

Mark Sowul
Mark Sowul

Reputation: 10600

With SQL 2012 and later, you could use TRY_CAST/TRY_CONVERT to try converting to a numeric type, e.g. TRY_CAST(answer AS float) IS NOT NULL -- note though that this will match scientific notation too (1+E34). (If you use decimal, then scientific notation won't match)

Upvotes: 2

beach
beach

Reputation: 8630

You can use the following to only include valid characters:

SQL

SELECT * FROM @Table
WHERE Col NOT LIKE '%[^0-9.]%'

Results

Col
---------
234.62
6435.23
2

Upvotes: 69

David Hall
David Hall

Reputation: 33143

Try something like this - it works for the cases you have mentioned.

select * from tbl
where answer like '%[0-9]%'
and answer not like '%[:]%'
and answer not like '%[A-Z]%'

Upvotes: 16

Adriaan Stander
Adriaan Stander

Reputation: 166376

You can try this

ISNUMERIC (Transact-SQL)

ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0.

DECLARE @Table TABLE(
        Col VARCHAR(50)
)

INSERT INTO @Table SELECT 'ABC' 
INSERT INTO @Table SELECT 'Italy' 
INSERT INTO @Table SELECT 'Apple' 
INSERT INTO @Table SELECT '234.62' 
INSERT INTO @Table SELECT '2:234:43:22' 
INSERT INTO @Table SELECT 'France' 
INSERT INTO @Table SELECT '6435.23'
INSERT INTO @Table SELECT '2' 
INSERT INTO @Table SELECT 'Lions'

SELECT  *
FROM    @Table
WHERE   ISNUMERIC(Col) = 1

Upvotes: 33

Related Questions