Search code examples
sqlsql-serverisnumeric

SQL Server 2014 - How to select rows with no numerical values in fields?

I'm trying to find Accounts that have nothing in the phone number field and nothing in the EmailAddress field. But when I use the ISNUMERIC function to filter our records with a phone number, I still get a few records with numbers in them.

My Code:

SELECT * FROM MyTable
WHERE (ISNUMERIC(DayTimePhone) = 0 OR DayTimePhone IS NULL)
  AND (DayTimePhone NOT LIKE '%[A-Z]%')
  AND ((EMailAddress IS NULL) OR (EMailAddress NOT LIKE '%[A-Z]%'))

The numbers I'm getting in the DayTimePhone field are: 

604.123.1123
(604) 123-1234
(604) 234-5678
(604) 345-6789
Solution

  • You could use LEN to filter the columns by length of characters in the columns and find all records where those fields are empty. Something like this perhaps?

    SELECT 
    * 
FROM MyTable
WHERE
    (LEN(ISNULL(DayTimePhone, '')) = 0 OR DayTimePhone IS NULL)
    AND ((EmailAddressIS NULL) OR (LEN(ISNULL(EmailAddress, '')) = 0))