LIKE - 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 2000 Forums

SQL Server Development (2000)

Author

Topic

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2010-11-20 : 04:33:40

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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 08:35:47

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.

Kristen
Test

22859 Posts

Posted - 2010-11-20 : 12:19:50

Nigel: Did you mean this?


where fld like '%[^' + char(32) + '-' + char(126)+ ']%'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 12:26:27

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.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-20 : 13:21:36

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/

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2010-11-21 : 07:30:02

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?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-21 : 13:04:14

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.

Kristen
Test

22859 Posts

Posted - 2010-11-21 : 17:10:21

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)

Kristen
Test

22859 Posts

Posted - 2010-11-21 : 17:23:26

Couldn't find the original thread, but this one may help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139513

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources