Range class in 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

General SQL Server Forums

New to SQL Server Programming

Range class in Like

Author

Topic

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2012-10-31 : 01:34:25

Hi All,

This is the condition in my where clause

like '%[^a-zA-Z]%')
The above condition includes only a-z and A-Z or any more characters?

my assumption this range a-z or A-Z also includes some of the characters like À Á Â Ã Ä Å æ ....

for example:
declare
@a varchar(20)
set @a='HÉLÈNE'
if (@a like '%[^a-zA-Z]%')
print ' contain invalid data'
else
print 'doesnot contain invalid data'

it is returning output as 'doesnot contain invalid data'
but i am expecting "contain invalid data" because of characters É and È in the @a variable.

please advise how to write like condition to exclude those kind(É and È) of characters in sql server2000.

Thanks..

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-31 : 01:37:10

Can't you use the collate clause as suggested previously - you probably won't be able to do this without a character by character test or a binary 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.

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2012-10-31 : 01:59:36

Nigelrivett thanks alot for your Continues support.

pls confirm can i use below script to know set of characters comes under a-z or A-Z range class.

declare
@a VARCHAR(100),
@i int
set @i=0
while(@i<255)
begin
SET @a =char(@i)

if (@a like '%[^a-zA-Z]%')
print @a+' contain invalid data and not in range a-z or A-Z'
else
print @a+' doesnot contain invalid data in range a-z or A-Z'

set @i=@i+1
end

M.MURALI kRISHNA

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-31 : 07:38:49

Depends on your collation. It will do the mapping before the check

try these
if (@a like '%[^a-zA-Z]%' collate Latin1_General_BIN)
if (@a like '%[^a-zA-Z]%' collate SQL_Latin1_General_Cp1_CS_AS)
if (@a like '%[^a-zA-Z]%' collate SQL_Latin1_General_Cp1_CS_AI)

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources