Query for unconventional people names in SQL Server

Question

I want to search for the people in a table (10 mil records +) with an unusual names with special characters such as some following samples:

Subject_Name (lastname, firstname):
*S-luis-Hernandez, Franciscos 
+, Zeferino 
-, Jose Antonios
., . 
., . .
/, Celsos
17, Herbert Baltazar
`, Irving 

I can filter the Subject_Name using like % but that's a lot of likes in the query. Is there a better way to pull those records from a large table?

Answer 1

SELECT *
FROM YourTable
where Subject_Name LIKE '%[^a-Z ]%'  collate Latin1_General_CI_AI

Answer 2

You should be able to easily find non-alpha characters using a LIKE statement:

---

EDIT:

I thought Subject_Name was your table... but I guess it's your column. Is both the first name and last name stored in that column, separated by a comma? In that case, we'd need to add the comma to the allowed characters... I'd also suggest splitting that one column into two.

SELECT * 
FROM YourTable
WHERE Subject_Name LIKE '%[^ a-zA-Z,]%'