1

I need to do a case sensitive comparison within a stored procedure in SQL Server 2008 but cannot work out how to achieve it.

I have tried the following code:

Declare @andJob  varchar(1)

select @andJob = (SELECT szUserdefined6 from inserted ) 

if  @andJob like 'y%'
begin
  /* Do Stuff */
end
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
joebohen
  • 145
  • 1
  • 14
  • Search the net for `collation sequences` : Strings (CHAR, VARCHAR, etc) are stored using different collation sequences, with the default one to use being a setting in the SQL Server. Some collation sequences are case-sensitive and some are case-insensitive. You have a server that is using a case-sensitive sequence. Either change the server setting, or manually define the collation sequence to use on that one field in that one table. But be cautious of mixing/comparing different sequences as it will require one to be translated in to the other. – MatBailie Apr 01 '14 at 09:21
  • I have set the field to Latin1_General_CS_AI and this has no impact on the query. – joebohen Apr 01 '14 at 10:23
  • Try explicitly forcing the collation of the `'y%'` too. Use Execution Plans to examine whether any conversations are occuring. – MatBailie Apr 01 '14 at 10:49
  • @MatBailie Regarding your first comment here, this operation has nothing to do with server / instance-level Collation. That level controls variable names but not their values. It is DB-level Collation that affects variable values. Also, please see my [answer](https://stackoverflow.com/a/50533301/577765) below. It _might_ be what you were suggesting in the comment directly above, but not entirely sure. – Solomon Rutzky May 25 '18 at 16:23
  • Hi @joebohen . I was just wondering if you have had a chance to review my [answer](https://stackoverflow.com/a/50533301/577765). I just updated it to include a simple example that can be copied and pasted to see how it works if you don't have the same environment available to you anymore (I do realize that it has been almost 5 years since you posted this question ;-). – Solomon Rutzky Jan 30 '19 at 04:26

3 Answers3

0

You can use COLLATION option to achieve this. Eg.Link Further can be found here at MSDN for a list of collation options.

S.N
  • 4,910
  • 5
  • 31
  • 51
0

Probably, declaring a collation will do the job. Look at these two questions to find a detailed explanation:

SQL Case Sensitive String Compare

SQL server ignore case in a where expression

Do one of these answers solve your problem?

Community
  • 1
  • 1
Tonia H
  • 99
  • 3
0

The problem is simply that string literals / constants and variables (which include parameters) use the current Database's default Collation. When working with a column, the column's Collation is used. Here you are storing the value from a column into a variable, so the Collation of the column no longer matters.

Since you are clearly in a Database that has a case-insensitive default Collation, you need to specify the COLLATE keyword as part of that comparison. Meaning:

IF ( @andJob LIKE 'y%' COLLATE Latin1_General_CS_AI )

That will override the default behavior of using the Database's Collation for that comparison.

For example:

IF (DB_ID(N'CaseInsensitive') IS NULL)
BEGIN
  CREATE DATABASE [CaseInsensitive] COLLATE Latin1_General_100_CI_AS;
  ALTER DATABASE [CaseInsensitive] SET RECOVERY SIMPLE;
END;
GO

USE [CaseInsensitive];

SELECT 1 WHERE 'YES' LIKE 'y%';
-- 1

SELECT 2 WHERE 'YES' LIKE 'y%' COLLATE Latin1_General_100_CS_AS;
-- (nothing)


/* -- Clean up:
USE [master];
DROP DATABASE [CaseInsensitive];
*/

Also, you are declaring the variable as varchar(1), which seems a bit small. It will truncate any value from szUserdefined6 to just the first character, and I'm not sure that this is the intended behavior.

ALSO, you need to be very careful when doing select @andJob = (SELECT szUserdefined6 from inserted ) in a Trigger. A Trigger that is executed from a multi-row DML operation will have one row per each affected row in the inserted and/or deleted tables. What you are doing here only captures the value of the final row in the inserted table, hence it only truly works if there is NEVER a multi-row INSERT or UPDATE against this table.

For more info on collations, encodings, etc, please visit: Collations Info

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171