Case-sensitivity in a stored procedure

2 Apr 2005 4:10 PM

Matthias S.

Hi there!

How can I toggle case-sensitivity within a stored procedure? I'd like to
compare 2 passed in parameters against fields in a table. The first
comparison is case-sensitive, the second is not.

How can I do that? By default it seems they're not case-sensitive.

Thanks in advance.

--
/Matthias

2 Apr 2005 5:10 PM

Jim Brandley

Not sure of your specific need, but something like this should work:

Select fieldname into vMyVariable where UPPER(otherfield) =
UPPER(vTargetValue);

Show quoteHide quote

"Matthias S." <postamt@_remove_emvoid_remove_.de> wrote in message
news:O1ERV55NFHA.3668@TK2MSFTNGP14.phx.gbl...
> Hi there!
>
> How can I toggle case-sensitivity within a stored procedure? I'd like to
> compare 2 passed in parameters against fields in a table. The first
> comparison is case-sensitive, the second is not.
>
> How can I do that? By default it seems they're not case-sensitive.
>
> Thanks in advance.
>
> --
> /Matthias

3 Apr 2005 11:03 AM

Matthias S.

Hi Jim,

thanks for your reply. Maybe I didn't get it clear. Here is what I want
to do:

SELECT * FROM myTable WHERE myField = 'Peter'

should return 'Peter', but not 'peter' or 'PETER'

If I use the UPPER method, I don't have a possibility to distinct
between upper and lowercase.

Thanks again for your reply.

/Matthias

Jim Brandley wrote:
Show quoteHide quote

> Not sure of your specific need, but something like this should work:
>
> Select fieldname into vMyVariable where UPPER(otherfield) =
> UPPER(vTargetValue);
>
> "Matthias S." <postamt@_remove_emvoid_remove_.de> wrote in message
> news:O1ERV55NFHA.3668@TK2MSFTNGP14.phx.gbl...
>
>>Hi there!
>>
>>How can I toggle case-sensitivity within a stored procedure? I'd like to
>>compare 2 passed in parameters against fields in a table. The first
>>comparison is case-sensitive, the second is not.
>>
>>How can I do that? By default it seems they're not case-sensitive.
>>
>>Thanks in advance.
>>
>>--
>>/Matthias
>
>
>

3 Apr 2005 11:39 AM

Stephany Young

Aha! Look how it becomes clear when you explain it properly.

This is a database design issue rather than a programming issue.

In SQL Server each database has a 'collation sequence' taken from a list
containing Latin1_General_CI_AS. This means the Latin1 character set,
General means the sort order, CI means case insentive and AS means accent
sensitive.

Unless specified otherwise each column defined in tables has this COLLATION.

Define column myTable.myField as case sensitive by applying COLLATION
Latin1_General_CS_AS or whichever one you want and voila.

How this is acheived on other RDBMS's I don't know, but I believe that you
can't do it in Jet.

Show quoteHide quote

"Matthias S." <postamt@_remove_emvoid_remove_.de> wrote in message
news:ek1azyDOFHA.3296@TK2MSFTNGP15.phx.gbl...
> Hi Jim,
>
> thanks for your reply. Maybe I didn't get it clear. Here is what I want to
> do:
>
> SELECT * FROM myTable WHERE myField = 'Peter'
>
> should return 'Peter', but not 'peter' or 'PETER'
>
> If I use the UPPER method, I don't have a possibility to distinct between
> upper and lowercase.
>
> Thanks again for your reply.
>
> /Matthias
>
> Jim Brandley wrote:
>> Not sure of your specific need, but something like this should work:
>>
>> Select fieldname into vMyVariable where UPPER(otherfield) =
>> UPPER(vTargetValue);
>>
>> "Matthias S." <postamt@_remove_emvoid_remove_.de> wrote in message
>> news:O1ERV55NFHA.3668@TK2MSFTNGP14.phx.gbl...
>>
>>>Hi there!
>>>
>>>How can I toggle case-sensitivity within a stored procedure? I'd like to
>>>compare 2 passed in parameters against fields in a table. The first
>>>comparison is case-sensitive, the second is not.
>>>
>>>How can I do that? By default it seems they're not case-sensitive.
>>>
>>>Thanks in advance.
>>>
>>>--
>>>/Matthias
>>
>>

3 Apr 2005 12:29 PM

Greg Low [MVP]

Hi Matthias,

In addition to what the others have mentioned, in SQL Server since SQL
Server 2000, you can specify a specific collation whenever you have a
comparison (equality, greater than, less than, etc.).

eg:

SELECT something
FROM somewhere
WHERE somecolumn = someothercolumn COLLATE somecollation

HTH,

--
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com

Show quoteHide quote

"Matthias S." <postamt@_remove_emvoid_remove_.de> wrote in message
news:ek1azyDOFHA.3296@TK2MSFTNGP15.phx.gbl...
> Hi Jim,
>
> thanks for your reply. Maybe I didn't get it clear. Here is what I want to
> do:
>
> SELECT * FROM myTable WHERE myField = 'Peter'
>
> should return 'Peter', but not 'peter' or 'PETER'
>
> If I use the UPPER method, I don't have a possibility to distinct between
> upper and lowercase.
>
> Thanks again for your reply.
>
> /Matthias
>
> Jim Brandley wrote:
>> Not sure of your specific need, but something like this should work:
>>
>> Select fieldname into vMyVariable where UPPER(otherfield) =
>> UPPER(vTargetValue);
>>
>> "Matthias S." <postamt@_remove_emvoid_remove_.de> wrote in message
>> news:O1ERV55NFHA.3668@TK2MSFTNGP14.phx.gbl...
>>
>>>Hi there!
>>>
>>>How can I toggle case-sensitivity within a stored procedure? I'd like to
>>>compare 2 passed in parameters against fields in a table. The first
>>>comparison is case-sensitive, the second is not.
>>>
>>>How can I do that? By default it seems they're not case-sensitive.
>>>
>>>Thanks in advance.
>>>
>>>--
>>>/Matthias
>>
>>

3 Apr 2005 2:11 PM

Matthias S.

Thank you guys, that helped!

/Matthias

Greg Low [MVP] wrote:
Show quoteHide quote

> Hi Matthias,
>
> In addition to what the others have mentioned, in SQL Server since SQL
> Server 2000, you can specify a specific collation whenever you have a
> comparison (equality, greater than, less than, etc.).
>
> eg:
>
> SELECT something
> FROM somewhere
> WHERE somecolumn = someothercolumn COLLATE somecollation
>
> HTH,
>

3 Apr 2005 11:54 AM

Mark Rae

"Matthias S." <postamt@_remove_emvoid_remove_.de> wrote in message
news:O1ERV55NFHA.3668@TK2MSFTNGP14.phx.gbl...

> How can I toggle case-sensitivity within a stored procedure? I'd like to
> compare 2 passed in parameters against fields in a table. The first
> comparison is case-sensitive, the second is not.
>
> How can I do that? By default it seems they're not case-sensitive.

SELECT 'Row' WHERE 'PETER' = 'Peter' -- returns one row
SELECT 'Row' WHERE CONVERT(varbinary, 'PETER') = CONVERT(varbinary,
'Peter') -- returns no rows