user1777929
user1777929

Reputation: 797

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

Upvotes: 0

Views: 44

Answers (2)

Alexandria Scott
Alexandria Scott

Reputation: 19

There are other characters than just numbers in those values, so they ISNUMERIC() returns 0. You could do a select to get the values without special characters and then run your code.

Upvotes: 0

mituw16
mituw16

Reputation: 5250

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))

Upvotes: 2

Related Questions