LIKE Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

LIKE Issue

Author

Topic

nasu
Yak Posting Veteran

50 Posts

Posted - 2013-05-13 : 04:30:03

I am trying to find a way to select records that have one or more 6-digit numbers in a varchar field and a LIKE statement seems to do the trick. However, I have a problem with selecting a specific record that should match.

I can select it using

SELECT * FROM myTable WHERE OldValue = '178230'

but not using

SELECT * FROM myTable WHERE OldValue LIKE '%[1-9][1-9][1-9][1-9][1-9][1-9]%'

This record only has the characters 178230 in the OldValue column as far as I can see. Another record with a single 6-digit number in it does get selected and also other records consisting of a mix of such a number and other characters. What could be special about the record I am not able to select?

nasu
Yak Posting Veteran

50 Posts

Posted - 2013-05-13 : 04:39:10

Oh, sorry, now I see what the problem is. I did not include 0 in the range of integers to look for.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-13 : 04:41:58

--See this illustration
DECLARE @tab TABLE (OldValue VARCHAR(30))
insert into @tab
SELECT '123456' union all
SELECT 'Only Chars' union all
SELECT '12ad34sad56' union all
SELECT '12saf3456' union all
SELECT '1a23sads456' union all
SELECT 'fz123a456asdf' union all
SELECT '12sa34sdsaad56'
SELECT * FROM @tab WHERE OldValue LIKE '%[1-9]%[1-9]%[1-9]%[1-9]%[1-9]%[1-9]%'

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources