Like operator and Regular Expressions

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

Like operator and Regular Expressions

Author

Topic

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2012-10-30 : 07:22:30

Hi All,
I have a condition in my where clause as shown below.

LIKE '%[^a-zA-Z.&0-9- '']%')

my intention is it should not accept any character other than alphabets,numbers0-9,dot,single quote,amperstant,eiphen and single space.

The query is:
set nocount on
declare
@MyString VARCHAR(100),
@i int
set @i=0
while(@i<255)
begin
SET @MyString = 'Mr'+cast(char(@i) as varchar(10))

IF (@MyString LIKE '%[^a-zA-Z.&0-9- '']%')

PRINT @mystring+' Contains "special" characters' + ' char('+cast(@i as varchar(20))+')'

ELSE
PRINT @mystring+' Does not contain "special" characters' + ' char('+cast(@i as varchar(20))+')'

set @i=@i+1
end

but my query is showing "Does not contain "special" characters"
for the characters from "char(192) to char(253)"
Sample list of characters are:
À
Á

please advise the above mentioned characters(from "char(192) to char(253)) comes under which group?

pls provide info about char(230) "æ" what it is? why it accepted?

Thanks.

M.MURALI kRISHNA

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-30 : 07:36:03

Check this link for first 256 ASCII codes
http://www.lookuptables.com/

--
Chandu

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-30 : 07:36:09

EDIT: IGNORE THIS. I MUST HAVE BEEN SMOKING SOMETHING, ALTHOUGH I CAN'T REMEMBER WHAT IT WAS.

You just have to reverse the logic and use NOT LIKE rather than LIKE. What your like clause is really saying, if @MyString is like something that is not in your list of characters.

declare
@MyString VARCHAR(100),
@i int
set @i=0
while(@i<255)
begin
SET @MyString = 'Mr'+cast(char(@i) as varchar(10))

IF (@MyString NOT LIKE '%[^a-zA-Z.&0-9- '']%')

PRINT @mystring+' Does not contain "special" characters' + ' char('+cast(@i as varchar(20))+')'

ELSE
PRINT @mystring+' Contains "special" characters' + ' char('+cast(@i as varchar(20))+')'

set @i=@i+1
end

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-30 : 07:41:48

It will depend on your collation - I suspect you are binary.
Try this

declare @a varchar(1000)

select @a = '%[^a-zA-Z.&0-9- '']%'

select 1 where CHAR(192) like @a collate Latin1_General_BIN
select 1 where CHAR(192) like @a collate SQL_Latin1_General_Cp1_CS_AS

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-10-30 : 07:53:35

1.) IF BEGIN ... END ELSE BEGIN ... END

2.) Looks to me like a-zA-Z is including characters like ý so this works:
@MyString LIKE '%[^abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ.&0-9- '']%'

Too old to Rock'n'Roll too young to die.
edit: maybe same for something like ¼ for 0-9

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2012-10-30 : 07:59:13

Thanks for quick reply.

Hi nigelrivett,
I executed the script i got below result set:

-----------
1

(1 row(s) affected)

-----------

(0 row(s) affected)

For first select returns result as "1"
Second select returns no result.

Hi sunitabeck:
with "not like" operator i am getting opposite result.

M.MURALI kRISHNA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-10-30 : 08:13:12

Nigel is right

This:

IF (@MyString LIKE '%[^a-zA-Z.&0-9- '']%' collate Latin1_General_BIN)

works

but think about BEGIN and END in your IF/ELSE block

Too old to Rock'n'Roll too young to die.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-30 : 08:16:21

It doesn't need a begin end for a single statement.

The issue is that the order of characters is decided by the collations. The code is mapped to a collated code and the like statement works on the result. If you have a binary collation then the ascii code will be used.

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-30 : 08:26:09

There are a few oddities with respect to this though

declare @a varchar(1000)

select @a = '%[^A-Z]%'

select 1 where CHAR(192) like @a collate Latin1_General_BIN -- yes
select 1 where CHAR(192) like @a collate SQL_Latin1_General_Cp1_CS_AS -- no

select @a = '%[^'+ char(192) + '-' + char(200) + ']%'

select 1 where CHAR(192) like @a collate Latin1_General_BIN -- no
select 1 where CHAR(192) like @a collate SQL_Latin1_General_Cp1_CS_AS -- no

select 1 where CHAR(192) = '%[A-B]%' collate Latin1_General_BIN -- no
select 1 where CHAR(192) like '%[A-B]%' collate SQL_Latin1_General_Cp1_CS_AS -- yes
select 1 where CHAR(192) like '%[A-A]%' collate SQL_Latin1_General_Cp1_CS_AS -- no

==========================================
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-30 : 09:06:40

Hi nigelrivett,

Here people are telling we are using collation "SQL_Latin1_General_CP1_CI_AS"

They are telling we don't change this collation.

i didn't get your last reply completely,can you please elabarate little bit more.

Thanks..

M.MURALI kRISHNA

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-30 : 09:11:34

It was just some examples of how to find out what is happening.

I'm not suggesting you changet he collation - just add the collate clause to your like statement to check for the data you want.

==========================================
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-30 : 12:12:32

Hi Webfred the solution which was given by you getting exact results but it is hard coded(we didn't mention all the alphabets).

And my instance collation is SQL_Latin1_General_Cp1_CI_AS means ascent sencitive "a" is not equal to " a` " but if i check condition as below.
if (@mystring like '%[^a-zA-z]%')
print 'special char'
else
print 'no special char'

Note:@mystring contain that a` character

i am getting result set as "no special char" pls advise why am i getting this?

Is there any other way in sql server version 2000 to exclude all the characters from "char(192) to char(253)".

M.MURALI kRISHNA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources