Searching for special characters

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

Searching for special characters

Author

Topic

disciple
Starting Member

27 Posts

Posted - 2009-02-02 : 17:11:12

Hi guys,

I've been looking around trying to find a solution to this, but haven't found anything I can use so far. I have a members table, and I need to run a query to find all usernames that have a special character in it. I've already limited the input field where they register to not accept such characters, however I need to go back through the database and find all the already existing logins with any characters that aren't alphanumeric.

I don't know much about regular expressions, though from what i've seen it appeared to be difficult to integrate it within SQL Server. Does anyone have any ideas?

Thanks for any help given.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-02 : 18:50:12

http://sqlserver2000.databases.aspfaq.com/how-do-i-search-for-special-characters-e-g-in-sql-server.html

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-02 : 21:22:54

One possibility is to use patindex function (which allows wild card characters). For example:

declare @a varchar(32);
set @a = 'abcd' + char(7) + 'efgh';
select patindex('%['+char(0)+'-'+char(47)+']%',@a)

This will find any non-alphanumeric characters whose ascii codes are 0 through 47. So, in this example, it will find the char(7) at position 5.

disciple
Starting Member

27 Posts

Posted - 2009-02-03 : 16:50:40

Thanks for the replies, I don't want to use the first example because I don't want to have to manually write out every special character to check for.

I'm a little unsure how to utilize sunitabeck's example. Could I so something like:

@a = SELECT LOGINID FROM MEMBERS
select patindex('%['+char(0)+'-'+char(47)+']%',@a)

I'm still a bit new to this, how would I implement checking every loginid against that pattern? Also, would there be a way I could expand what it checks against? I mean like all chars between 0 and 47 and also 80-93 along with 98 (numbers chosen randomly)? Once again, I appreciate the help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-03 : 17:08:38

quote:
Originally posted by disciple

Thanks for the replies, I don't want to use the first example because I don't want to have to manually write out every special character to check for.

I'm a little unsure how to utilize sunitabeck's example. Could I so something like:

@a = SELECT LOGINID FROM MEMBERS
select patindex('%['+char(0)+'-'+char(47)+']%',@a)

I'm still a bit new to this, how would I implement checking every loginid against that pattern? Also, would there be a way I could expand what it checks against? I mean like all chars between 0 and 47 and also 80-93 along with 98 (numbers chosen randomly)? Once again, I appreciate the help.

@a was only used to show a running example.
To modify this example for your table to find the bad logins, I think:

SELECT LOGINID FROM MEMBERS
where patindex('%['+char(0)+'-'+char(47)+']%',LOGINID) > 0

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources