Special characters in a string

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

Transact-SQL (2000)

Special characters in a string

Author

Topic

bpuccha
Starting Member

34 Posts

Posted - 2012-09-13 : 15:35:53

Hi,

How to check only the below special characters in a string

"\", "/", ":", "?", CHR(34), "*", "<", ">", "|", "'", "." and ","

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-13 : 15:44:29

If you are interested just in detecting the existence of those special characters, you can do something like this - I have inserted only a few of the characters in your list in the like clause, you will need to insert them all.

DECLARE @string VARCHAR(32) = 'abcdr:x';
SELECT CASE 
            WHEN @string LIKE '%[\/:?]%' THEN 'Special chars exist'
            ELSE 'No special chars found'
       END

If you need more information, what are the kinds of information you need?

bpuccha
Starting Member

34 Posts

Posted - 2012-09-13 : 15:50:35

I just want to detect whether it has any of those special characters or not, I tried that but it not allowing "'" and CHAR(34)(").

How to include those to into the list?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 16:17:07

quote:
Originally posted by bpuccha

I just want to detect whether it has any of those special characters or not, I tried that but it not allowing "'" and CHAR(34)(").

How to include those to into the list?

for quotes you need to escape it while using

so use
'''' for including a ' in a string

see

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bpuccha
Starting Member

34 Posts

Posted - 2012-09-13 : 16:28:00

tried below code..It worked fine..Thanks

if replace(substring('@tmpvariable',1,10),' ' ,'') like '%[\/:?*<>|.,''''"]%'
begin
print 'it has special chars'
end
else
begin
print 'it doesnt have special chars'
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 16:48:11

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources