SQL Query for Finding Rows with Special Characters Only

SQL query for finding rows with special characters only

SELECT
    TemplateID,
    Body
FROM
    #Template
WHERE
    Body LIKE '%[^0-9a-zA-Z ]%'

The stuff between the brackets says numbers (0-9), lowercase alphas (a-z), uppercase alphas (A-Z) and the space. The "^" makes that a "NOT" one of these things. Note that this is different though than NOT LIKE '%[0-9a-zA-Z ]%'

Get rows which contain exactly one special character

One option would be using REGEXP_COUNT and at most one underscore is needed then use

WHERE REGEXP_COUNT( col, '_' ) <= 1

or strictly one underscore should exist then use

WHERE REGEXP_COUNT( col, '_' ) = 1

Find all special characters in a column in SQL Server 2008

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 [].

Find rows which contain special characters except underscore and space

I would do this with the help of a collation like Latin1_General_BIN like this:

SELECT *
FROM   tabl
WHERE  val COLLATE Latin1_General_BIN LIKE '%[^A-Za-z0-9 _]%'

It would seem easier this way because BIN collations are both case-sensitive and accent-sensitive and, moreover, accented characters are collated separately from non-accented ones. The latter means that it is easy to specify non-accented letters in the form of a range. (But case sensitivity means you also have to specify letters of both cases explicitly, as you can see above.)

How to find rows with only special characters on a field using MySQL?

You can use

SELECT * FROM table WHERE col REGEXP '^[^[:alnum:][:space:]_]+$'

See the regex demo.

The regex pattern breakdown:

• ^ - start of string
• [^ - start of the negated character class that matches any character other than defined in this class
  
  
  • [:alnum:] - letters and digits
  • [:space:] - whitespace
  • _ - underscore
• ] - end of the negated character class
• + - the characters matched by the negated character class must be 1 or more occurrences
• $ - end of string

SQL cannot select rows with special characters

Unicode literal strings need to be prefixed with N:

SELECT TOP (1000) [name] 
FROM [mpnew].[dbo].[arts] 
WHERE [grupa] = N'NAMJEŠTAJ'
                ^

---

Related Topics