Help with a Query

In a stored procedure that performs a search on a Client table, one
condition in my WHERE clause is:
WHERE
(ISNULL(ClientPhone, '%%') LIKE '%' + COALESCE(@.ClientPhone,
ClientPhone, '') + '%')
This allows me to search on the ClientPhone column depending on whether
or not the @.ClientPhone parameter was NULL or not.
This works great, except that I recently noticed that if I leave the
@.ClientPhone parameter as NULL, the search results are exluding rows
which have the ClientPhone formatted as [1] 310-555-1212
So, any row in which the phone number has the [ and ] characters is not
being returned.
How should my condition in the WHERE clause change to handle this?
Thank you for your helpOn 4 Oct 2005 11:09:23 -0700, george.durzi@.gmail.com wrote:

>In a stored procedure that performs a search on a Client table, one
>condition in my WHERE clause is:
>WHERE
> (ISNULL(ClientPhone, '%%') LIKE '%' + COALESCE(@.ClientPhone,
>ClientPhone, '') + '%')
>This allows me to search on the ClientPhone column depending on whether
>or not the @.ClientPhone parameter was NULL or not.
>This works great, except that I recently noticed that if I leave the
>@.ClientPhone parameter as NULL, the search results are exluding rows
>which have the ClientPhone formatted as [1] 310-555-1212
>So, any row in which the phone number has the [ and ] characters is not
>being returned.
>How should my condition in the WHERE clause change to handle this?
>Thank you for your help
Hi George,
The reason is that the characters [ and ] in a LIKE expression have a
special meaning (see description in Books Online for details).
Here's a way to get around this:
WHERE
REPLACE(REPLACE(COALESCE(ClientPhone, '%%'), '[', '('), ']', ')')
LIKE
REPLACE(REPLACE('%' + COALESCE(@.ClientPhone, ClientPhone, '') + '%'),
'[', '('), ']', ')')
Ugly? Yes.
If I were you, I'd change the existing square brackets in your data to
round brackets (as is the custom for phone numbers), and add a CHECK
constraint on the column to ensure no new square brackets are entered.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for your reply. As you recommended, I'll clean up the data to
get rid of the [ and ] in the ClientPhone column. I'll also add the
CHECK constraint to prevent those from getting into that column..
Thanks again|||Please provide DDL and sample data, since there must be a simpler way to do
what you need. In the mean time try replacing "[" and "]" with "(" and ")"
inside the where clause.
Read more about wildcard characters here:
http://msdn.microsoft.com/library/d...br />
115x.asp
A far better solution involves a few extra columns inside the table (or a
view) where individual parts of the phone number are stored, since it is
quite obvious that on of your business requirements might benefit greatly
from this.
MLsql