Search code examples
sqlnumbersspecial-characterssuffixfilter-run-excluding

SQL query to exclude numbers, brackets or special characters at the end of a name

I'm trying to exclude all names with numbers in it, but still include certain special characters like: ()-'

So far, i've got this query:

SELECT FirstName FROM Client WHERE FirstName NOT LIKE '%[^0-9]%';

This excludes most names with numbers in it, however, a few like the following are still coming through (not being excluded):

enter image description here

How do i exclude these from above, but still keep rows that look like the ones below:

enter image description here

Thank you in advance!

Solution

  • I'm trying to exclude all names with numbers in it.

    Assuming you are using SQL Server, your logic has too many negations. You want:

    WHERE FirstName NOT LIKE '%[0-9]%';

    This translates to FirstName not having any number in it.

    If you want names that are only alphabetical and spaces, then use:

    WHERE FirstName NOT LIKE '%[^a-zA-Z ]%';

    Your version simply states that the string has no character which is not like a digit -- that is, it consists only of digits.