Case-sensitivity in a stored procedure

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

 

[Jim Brandley]

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

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

 

[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

 

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

 

[Mark Rae]

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

 

[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,

 

[Matthias S.]

Thank you guys, that helped!

/Matthias