|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Case-sensitivity in a stored procedureHi 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 Not sure of your specific need, but something like this should work:
Select fieldname into vMyVariable where UPPER(otherfield) = UPPER(vTargetValue); Show 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 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 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 > > > 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 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 >> >> 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, Show 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 >> >> Thank you guys, that helped!
/Matthias Greg Low [MVP] wrote: Show 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, > "Matthias S." <postamt@_remove_emvoid_remove_.de> wrote in message SELECT 'Row' WHERE 'PETER' = 'Peter' -- returns one rownews: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 CONVERT(varbinary, 'PETER') = CONVERT(varbinary, 'Peter') -- returns no rows  |
|||||||||||||||||||||||
Other interesting topics