SQL server 2000 Like Statement Usage

Question

I have a SQL statement that looks like:

SELECT [Phone]
FROM [Table]
WHERE
(
    [Phone] LIKE '[A-Z][a-z]'
    OR [Phone] = 'N/A'
    OR [Phone] LIKE '[0]'
)

The part I'm having trouble with is the where statement with the "LIKEs". I've seen SQL statements where authors used likes statements in the way I'm using them above. At first, I thought this might be a version of Regular Expressions but I've since learned.

Is anyone familiar with using like statements in such a way. Note: the "N/A" is working fine.

What I need to match is phone numbers that have characters. Or phone numbers which contain nothing but zero.

Thank you much,
Frank

Answer 1

Check here.

[] matches a range of characters.

I think you want something like this:

SELECT [Phone]
FROM [Table]
WHERE
(
    [Phone] LIKE '%[A-Z]%'
    OR [Phone] LIKE '%[a-z]%'
    OR [Phone] = 'N/A'
    OR [Phone] LIKE '0'
)

Answer 2

Try using the t-sql ISNUMERIC function. That will show you which ones are/are not numeric.

You may also need to TRIM or REPLACE spaces to get what you want.

For example, to find valid phone numbers, replace spaces with '', test with ISNUMERIC, and test with LEN.

Although I will warn you, this will be tedious if you have to deal with international phone numbers.

The thing to note with your SQL above, is that SQL Server doesn't understand Regex.