How to detect if a string contains special characters like #,$,^,&,*,@,! etc (non-alphanumeric characters) in SQL server 2005?
Asked
Active
Viewed 2.1e+01k times
42
-
3What do you consider *special?* – Joey Apr 01 '10 at 07:20
-
3Every character is special in its own way. You're going to have to be more specific. – Ignacio Vazquez-Abrams Apr 01 '10 at 07:21
-
1Ok, now it becomes »What does the ›etc.‹ refer to?«. – Joey Apr 01 '10 at 07:25
-
What flavour SQL are you using? SQL Server? mySQL?... – AdaTheDev Apr 01 '10 at 07:27
3 Answers
63
Assuming SQL Server:
e.g. if you class special characters as anything NOT alphanumeric:
DECLARE @MyString VARCHAR(100)
SET @MyString = 'adgkjb$'
IF (@MyString LIKE '%[^a-zA-Z0-9]%')
PRINT 'Contains "special" characters'
ELSE
PRINT 'Does not contain "special" characters'
Just add to other characters you don't class as special, inside the square brackets

AdaTheDev
- 142,592
- 28
- 206
- 200
-
6This answer confused me at first, because it uses `LIKE` but the ^ character in the brackets actually means 'NOT' – Baodad Aug 22 '14 at 21:22
-
1@AdaTheDev how do we exclude "-" from special characters in this scenario? – aaronmallen Mar 27 '15 at 18:44
-
@aaronmallen, to exclude dashes from special characters, use `IF (@MyString LIKE '%[^a-zA-Z0-9-]%')` – Baodad Aug 31 '16 at 22:22
-
-
@aaronmallen To expand on Baodad's answer, characters that have a special meaning within square brackets can be included as characters to match by placing them somewhere where the special meaning doesn't apply. So for '-', this would be at the very start or end of the list of characters. – Stewart Mar 25 '20 at 09:40
19
One way is for each special characters to be checked separately:
SELECT * FROM tableName
WHERE columnName LIKE "%#%" OR columnName LIKE "%$%" OR (etc.)

Akaisteph7
- 5,034
- 2
- 20
- 43

Brendan Long
- 53,280
- 21
- 146
- 188
-
Nice, i used this code to simplify my problem. I was looking for this **á** : `declare @letra char(1) set @letra = 'á' SELECT col1 ,col2 ,col3 FROM table WHERE col1 LIKE '%' + @letra + '%' OR col2 LIKE '%' + @letra + '%' OR col3 like '%' + @letra + '%' ` – Bachask8 Oct 02 '17 at 22:44
1
In postgresql you can use regular expressions in WHERE clause. Check http://www.postgresql.org/docs/8.4/static/functions-matching.html
MySQL has something simmilar: http://dev.mysql.com/doc/refman/5.5/en/regexp.html

skyman
- 2,422
- 17
- 16