32

I need to find the occurrence of all special characters in a column in SQL Server 2008. So, I don't care about A, B, C ... 8, 9, 0, but I do care about !, @, &,, etc.

The easiest way to do so, in my mind, would exclude A, B, C, ... 8, 9, 0, but if I wrote a statement to exclude those, I would miss entries that had ! and A. So, it seems to me that I would have to get a list of every non-alphabet / non-number character, then run a SELECT with a LIKE and Wildcard qualifiers.

Here is what I would run:

SELECT Col1
FROM TABLE
WHERE Col1 LIKE ('!', '@', '#', '$', '%'....)

However, I don't think you can run multiple qualifiers, can you? Is there a way I could accomplish this?

mikebmassey
  • 8,354
  • 26
  • 70
  • 95

4 Answers4

56

Negatives are your friend here:

SELECT Col1
FROM TABLE
WHERE Col1 like '%[^a-Z0-9]%'

Which says that you want any rows where Col1 consists of any number of characters, then one character not in the set a-Z0-9, and then any number of characters.

If you have a case sensitive collation, it's important that you use a range that includes both upper and lower case A, a, Z and z, which is what I've given (originally I had it the wrong way around. a comes before A. Z comes after z)

Or, to put it another way, you could have written your original WHERE as:

Col1 LIKE '%[!@#$%]%'

But, as you observed, you'd need to know all of the characters to include in the [].

Smit Patel
  • 2,992
  • 1
  • 26
  • 44
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • 1
    This looks like this worked.... 2 questions though: What does the `^` do? And, with the string `A-z0-9`, why should there not be a comma between the search criteria, like this: `^A-z,0-9`? – mikebmassey Oct 15 '12 at 14:42
  • 1
    @Andomar - yep, corrected. I was initially following my pattern for doing `IsNumeric`, and forgot that we actually wanted characters outside the range. – Damien_The_Unbeliever Oct 15 '12 at 14:43
  • 3
    @mikebmassey - `^` (as the first character inside `[]`) says "anything *not* in this range. The `[]` block can just contain individual characters - you just put each possible character in turn. The above is equivalent to `[^aAbBcCdD....yYzZ0123456789'`. If you said `[^a-Z,0-9]` then you're just allowing `,` as another character that will not be highlighted. – Damien_The_Unbeliever Oct 15 '12 at 14:44
  • 1
    Thanks for that explanation - if I wanted to exclude spaces from that list, is there a way to add that to the string? Maybe something like `[^a-z0-9, " "]`? – mikebmassey Oct 15 '12 at 14:47
  • @mikebmassey - Nope, just include a space character: `[^a-Z0-9 ]`. Your example would allow `,`, space (included twice in your range, not treated as an error) and `"` (ditto). – Damien_The_Unbeliever Oct 15 '12 at 14:49
6

The following transact SQL script works for all languages (international). The solution is not to check for alphanumeric but to check for not containing special characters.

DECLARE @teststring nvarchar(max)
SET @teststring = 'Test''Me'
SELECT 'IS ALPHANUMERIC: ' + @teststring
WHERE @teststring NOT LIKE '%[-!#%&+,./:;<=>@`{|}~"()*\\\_\^\?\[\]\'']%' {ESCAPE '\'}
Manolis Spanakis
  • 61
  • 1
  • 1
3
Select * from TableName Where ColumnName LIKE '%[^A-Za-z0-9, ]%'

This will give you all the row which contains any special character.

Asheesh Thakur
  • 31
  • 5
  • 3
    An almost identical, but more detailed answer was given 5+ years ago on this. Why the unneeded answer? – gilliduck Feb 23 '18 at 18:27
1
select count(*) from dbo.tablename where address_line_1 LIKE '%[\'']%' {eSCAPE'\'}
Adrian W
  • 4,563
  • 11
  • 38
  • 52
user11380160
  • 11
  • 1