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

Help understanding generated MDX WHERE clause

When your report datasource is a cube, MDX is generated when you use the design view. In the MDX editor the generated MDX can be viewed. Using parameters I always get a where clause with code like the following:

IIF( STRTOSET(@.OrgLevelname, CONSTRAINED).Count = 1, STRTOSET(@.OrgLevelname, CONSTRAINED), [Organisation].[Level 2 name].currentmember )

I like to understand what is generated. Is there something I can read on the generated WHERE clause (I do understand the generated SELECT and FROM clauses)? Or can someone shed a light on it?

Why does the MDX need to branch on 'Count = 1' In what way does the result slice my data when Count = 1 or when Count <> 1?

Thanks,
Henk

Really no MDX guru around who can explain this to me?|||

Henk,

I would suggest posting your MDX question in the SQL Server Analysis Services forum at this url:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1

|||Thanks, I'll do that.

Help translating Access SQL to T-SQL

Can someone tell me what this Where clause (from MS Access) should be T-SQL?

Basically, if the value in the [reason] field contains 'DIST' then the row should return as long as the value in [movement] is greater than or equal to 1.

Where IIf([reason] LIKE '%DIST%',Val([movement]),1)>=1

Thanks!

something like this. "iif" is not a valid sql fucntion

where reason like '%dist%' and movement=> 1

val is also not valid so you may use cast or convert

therefore

where reason like '%dist%' and cast (movement, money) => 1

|||

You can do below:

where case when reason like '%DIST%' then sign(Movement) end = 1

-- or

where reason like '%DIST%'

and sign(Movement) = 1

-- or below which will use index on Movement if available

where reason like '%DIST%'

and Movement >= 1

|||

Replace the IIF() with Case:

Case when [reason] like '%DIST%' then...

Am I correct in intrepreting Val() as converting to a number (saves me a google)? Then you want to use Convert().

Final syntax:

Where Case When [reason] Like '%Dist%' then Convert(int, [movement] ) Else 1 End >= 1

You could also use NULL with the Else case.

....Guess I'm a slow typist...

|||

Thanks anomolous! That was the right answer. I just had to change int to real because [movement] holds a decimal value.

Yes, Val() in Access converts a string to a number And if it's NULL, Val() will return 0. Will Convert() return a 0 for a NULL value? Or do I need to use a different function so that I get a 0 if [movement] holds a NULL value?

|||

You have to use coalesce or isnull to check for NULL values. All built-ins return NULL for NULL input. Do something like:

where reason like '%DIST%'

and cast(coalesce(Movement, '') as int) >= 1

Note that CAST or CONVERT in TSQL will return error if the value cannot be converted. I don't know the behavior of VAL in Access. So in that case, you will have to do additional checks like:

where reason like '%DIST%'

and cast isnumeric(Movement) when 1 then cast(coalesce(Movement, '') as int) end >= 1

Even use of ISNUMERIC will not work for all cases since it checks for integer, numeric and money data type conversion semantics. So it is possible that you could have values that can convert to money but not int. So you will be better off actually modifying the schema such that Movement column is integer and it stores only integer values. Mixing different data types in a string column and manipulating it using TSQL is problematic in lot of ways. And it is often done incorrectly leading to bad performance due to conversions, wrong results, run-time errors and so on.