RegEx query not working

Question

Post reply

RegEx query not working

andegre

SSCertifiable

Points: 5875
More actions
September 23, 2010 at 2:38 pm

#227177

I'm trying to query records that are strictly integers via a regex query (not with real regex, just what sql server provides).
Here's what I've tried to do:
Select * From Table Where Column1 Like '[0-9]#'
Select * From Table Where Coumn1 Not Like '[^0-9]'
Neither of these work, can anyone tell me what I'm doing wrong with my RegEx?
TIA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

andegre SSCertifiable Points: 5875 More actions · Answer 1

Ok, I forgot about the ISNUMERIC function, but let's say that didn't exist...

LutzM SSC Guru Points: 107049 More actions · Answer 2

Be careful when using ISNUMERIC...

Here's an example for using ISNUMERIC vs. REGEX:

DECLARE @tbl TABLE

(

val VARCHAR(10)

)

INSERT INTO @tbl

SELECT '1e0' UNION ALL

SELECT '1w0' UNION ALL

SELECT '1d0' UNION ALL

SELECT '010' UNION ALL

SELECT '10e' UNION ALL

SELECT '10d' UNION ALL

SELECT '101' UNION ALL

SELECT '1077k' UNION ALL

SELECT '10s'

SELECT * FROM @tbl WHERE ISNUMERIC(val) = 1

SELECT * FROM @tbl WHERE val NOT LIKE '%[^0-9]%'

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

andegre SSCertifiable Points: 5875 More actions · Answer 3

Thanks Lutz.

I'm guessing the 1e0 is scientific notation, but what's the 1d0?

winash SSChampion Points: 11446 More actions · Answer 4

If you need -ve integers also try something like this:

DECLARE @T TABLE(Val varchar(30));

INSERT @T(Val)

SELECT '100'

UNION ALL

SELECT '10.5'

UNION ALL

SELECT 'AAA'

UNION ALL

SELECT 'BBB'

UNION ALL

SELECT '-100'

UNION ALL

SELECT '-A'

UNION ALL

SELECT '100-'

UNION ALL

SELECT '55'

SELECT * FROM @T;

SELECT * FROM @T WHERE (Val NOT LIKE '%[^0-9]%' OR Val LIKE '-%[0-9]%')

andegre SSCertifiable Points: 5875 More actions · Answer 5

Another question related to my problem...

I'm trying to join 2 tables, 1 table the field I want is defined as a varchar and I want to join it to the other table's primary key. I'm trying to do something like this:

Select *

From Table1

Where Column1 In

(

Select column3

From Table2

Where column3 Not Like '%[^0-9]%'

)

The problem I'm getting is, there are some values in column3 of Table2 that are larger than the int max value, so I tried converting to bigint, but that gives me an "arithmetic overflow error converting expression to data type bigint'.

Any suggestions?