patternsqlMinor
Dependant T-SQL comparison collation
Viewed 0 times
sqlcollationdependantcomparison
Problem
Is there any way to set the collation for a string comparison based on an independent variable or column value? Something akin to
or
SELECT COL1, col2
FROM [TABLE]
WHERE COL3 LIKE '%string%'
COLLATE CASE CASE_SENSITIVE
WHEN 1 THEN SQL_Latin1_General_CP1_CS_AS
ELSE SQL_Latin1_General_CP1_CI_AS
ENDor
DECLARE @collation AS VARCHAR(50)
SET @collation = 'SQL_Latin1_General_CP1_CI_AS'
SELECT COL1, col2
FROM [TABLE]
WHERE COL3 LIKE '%string%'
COLLATE @collationSolution
Along the lines of this with 2 where conditions.
One will be become
One will be become
'%string%' LIKE '%string%' which is always trueWHERE
CASE WHEN @casesenstive = 1 THEN COL3 ELSE '%string%' END
COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%string%'
AND
CASE WHEN @casesenstive = 0 THEN COL3 ELSE '%string%' END
COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%string%'Code Snippets
WHERE
CASE WHEN @casesenstive = 1 THEN COL3 ELSE '%string%' END
COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%string%'
AND
CASE WHEN @casesenstive = 0 THEN COL3 ELSE '%string%' END
COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%string%'Context
StackExchange Database Administrators Q#2177, answer score: 5
Revisions (0)
No revisions yet.