Reputation: 797
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
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
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