42

How to detect if a string contains special characters like #,$,^,&,*,@,! etc (non-alphanumeric characters) in SQL server 2005?

Manish
  • 6,106
  • 19
  • 64
  • 90

3 Answers3

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
  • 6
    This 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
  • how do we exclude space – Mohit Shah Nov 17 '16 at 10:07
  • @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