How to ignore case-sensitivity for the input value of a parameter

[prashant_nz]

Hi,

I am using person's name as a parameter in my report. I want to input the name in any case (upper or lower) and get the same result. I am not sure if we can do that or not.

Can some one please advise. Help will be appreciated.

Cheers

Prashant

[cbarlow3]

I think it depends on what version of SQL (assuming your query is SQL) you use, but I found the following link, which might help out:

http://aspadvice.com/blogs/ssmith/archive/2007/09/30/Case-Sensitive-or-Insensitive-SQL-Query.aspx

It essentially recommends that you change your query from something like this:

SELECT userName, userSSN, userTitle, etc.

FROM userTable

WHERE userName = $P{inputUserName}

to something like this:

 

SELECT userName, userSSN, userTitle, etc.

FROM userTable

WHERE userName = $P{inputUserName} COLLATE SQL_Latin1_General_CP1_CI_AS

I've never used the COLLATE command personally, but it's a start!

Carl

[mdahlman]

Carl's answer probably works. (I'm not sure if it works in all or most SQL flavors.) What I've seen more commonly is this variation:

WHERE lower(userName) = lower($P{inputUserName})

That has performance drawbacks compared with not performing the lower() function... but in lots of cases that gets you the data you need and the performance just doesn't matter very much.

Prashant doesn't mention anything about the data source (even if it's SQL) so it's not certain that this will meet all needs.

Regards,
Matt

 

[cbarlow3]

I was hoping SQL had a lower() and upper() string function like that, but for some reason when I tried to find it before, I found that odd COLLATE command instead.  I notice that some versions of SQL use LCASE() and UCASE() instead of lower() and upper(), by the way.

Carl