Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

migrated from serverfault.com Apr 13 '11 at 18:57

This question came from our site for professional system and network administrators.

1 Answer

up vote 4 down vote accepted

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%'
share|improve this answer
    
Nice one. Great for a binary condition, but is there any way to switch collation on a variable to accommodate several collations, accent sensitivity, case, etc other than doing nested case statements? –  Laramie Apr 13 '11 at 19:19
    
@laramie: no, sorry. it's "hard coded" –  gbn Apr 13 '11 at 19:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.