CODEWITHSUNDEEP
sqlsql-server
Frank V
Frank V

Reputation: 25429

SQL server 2000 Like Statement Usage

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 like 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.

Upvotes: 0

Views: 2580

Answers (2)

Forgotten Semicolon
Forgotten Semicolon

Reputation: 14110

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

Upvotes: 5

therealhoff
therealhoff

Reputation: 2375

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.

Upvotes: 2

Related Questions