HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Dependant T-SQL comparison collation

Submitted by: @import:stackexchange-dba··
0
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

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 
            END


or

DECLARE @collation AS VARCHAR(50)
SET @collation = 'SQL_Latin1_General_CP1_CI_AS' 

SELECT COL1, col2
FROM [TABLE]
WHERE COL3 LIKE '%string%'
    COLLATE @collation

Solution

Along the lines of this with 2 where conditions.

One will be become '%string%' LIKE '%string%' which is always true

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%'

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.