How to display rows containing special characters in a certain column [closed]

Posted on

Question :

I have a table with the column f_Name. The column f_name sometimes contains special characters and numbers as in the examples below:

f_name
-----------
Pan23's 
Ram'kumar
lawer"s()

I want to retrieve all the values of f_name which have a special character or number.

Answer :

Try This:

SELECT F_name  FROM tablename WHERE F_name LIKE '%[^a-Z0-9]%'

What this does is search for

%.....% : a string
[.....] : that contains any of the mentioned characters 
^       : well actually none of the mentioned characters
a-Z     : from lower-case a through to upper-case Z
0-9     : or any digit

Which results in a search for any character other than a-z, A-Z or 0-9.
Strings that match this would be:

test'user
something?else
I#can#fly

Reference: LIKE (Transact-SQL) (Microsoft | SQL Docs)

After re-reading your question, I would use just:

SELECT F_name  FROM tablename WHERE F_name LIKE '%[^a-Z]%'

…because you want to retrieve rows with numbers too.

Leave a Reply

Your email address will not be published.