SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 LIKE Issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nasu
Yak Posting Veteran

Sweden
50 Posts

Posted - 05/13/2013 :  04:30:03  Show Profile  Click to see nasu's MSN Messenger address  Reply with Quote
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

Sweden
50 Posts

Posted - 05/13/2013 :  04:39:10  Show Profile  Click to see nasu's MSN Messenger address  Reply with Quote
Oh, sorry, now I see what the problem is. I did not include 0 in the range of integers to look for.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 05/13/2013 :  04:41:58  Show Profile  Reply with Quote
--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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000