CODEWITHSUNDEEP
sqlsql-server-2005
joe
joe

Reputation: 1473

how to pull records with special characters in SQL (2005) Server?

I am trying to pull records that contain special characters like:

*,  +,  -, . (dot), ',  , /,  0-9

But did not have much sucess so far.

The column is called People_Name and the names supposed to be in this format: Lastname, Firstname. For example, Smith, Johnson. However, due to data entry error, some names show up as:

 681, Smith,  *S-Sean, Tom,   +, John,   -, Tim,  ., .,  /, Celo,  0-9999,
 Maria,  ', Irving, and sometime as 'Unknown, Alexis.

My current query (SQL Server 2005) is 

Select People_name
  from mytable
 where (People_name LIKE '%*%' 
    OR People_name LIKE '%-,%'
    OR People_name LIKE '%+%'
    OR People_name LIKE '%/%'
    OR People_name LIKE '%unknown%')

I can keep adding the LIKE list but I am sure there is a way in SQL server that can pull the records with those special characters. With more than 10 million records in the table, it is quite repeated to sort and look for those special characters then use the LIKE operator like above.

Can some one please look into it?

Upvotes: 0

Views: 1298

Answers (1)

roman
roman

Reputation: 117606

You can try 

select People_name
from mytable
where People_name like '%[^a-zA-Z ,]%'

SQL FIDDLE Example

Upvotes: 1

Related Questions