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 2000 Forums
 SQL Server Development (2000)
 LIKE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

daidaluus
Yak Posting Veteran

73 Posts

Posted - 11/20/2010 :  04:33:40  Show Profile  Reply with Quote
How can i write a LIKE comparison to find rows that have ascii characters less than 32 and greater than 126 in a column?

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/20/2010 :  08:35:47  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
where fld like '%[^' + char(97) + '-' + char(126)+ ']%'

like '%[x]%' seearches for an x in the string
like '%[^x]%' seearches for any not x in the string
like '%[^a-x]%' seearches for any charcter not between a and x in the string

from that it's just a matter of forming the strig to provide the check.



==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 11/20/2010 :  12:19:50  Show Profile  Reply with Quote
Nigel: Did you mean this?

where fld like '%[^' + char(32) + '-' + char(126)+ ']%'
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/20/2010 :  12:26:27  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
yep - don't know wher I got 97 from.
oh - it's 'a'.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/20/2010 :  13:21:36  Show Profile  Reply with Quote
I would like to know WHY?

PLEASE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

daidaluus
Yak Posting Veteran

73 Posts

Posted - 11/21/2010 :  07:30:02  Show Profile  Reply with Quote
I tried LIKE in so many way embeded with CHAR() function but none of them returned correct resultset. here is an example:

SELECT *
FROM (
SELECT 'abcf' Col UNION ALL
SELECT CHAR(210) + CHAR(167)
) a
WHERE Col LIKE '%[^' + CHAR(32) + '-' + CHAR(126)+ ']%'

which returns the two rows that is wrong. Can anybody tell me where is my mistake?
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/21/2010 :  13:04:14  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
works with
'%[^' + CHAR(48) + '-' + CHAR(122)+ ']%'
which is
'%[^0-z]%'

You can then add the others individually
'%[^0-z'+char(32)+char(33)+...+']%'

Might be worth investigating why it's happening. Could be that the code pages used mean that the ascii codes map to something that is not in order.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 11/21/2010 :  17:10:21  Show Profile  Reply with Quote
COLLATION will play merry hell with this. Force the collation to Binary and it should be fine (although I do remember a weird report here a year or so back which was clearly a Collation issue but we never got to the bottom of why it was happening)
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 11/21/2010 :  17:23:26  Show Profile  Reply with Quote
Couldn't find the original thread, but this one may help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139513
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.08 seconds. Powered By: Snitz Forums 2000