Question
Most of the SQL Server installations are installed with the default collation which is case insensitive. This means that SQL Server ignores the case of the characters and treats the string 'Harish Kumar' equal to the string 'harish kumar'. If you need to differentiate these values and are unable to change the collation at the server, database or column level, how can you differentiate these values?
Answer
1) As you can see, this SQL Server has a case insensitive collation i.e. CI.
SELECT SERVERPROPERTY ('Collation')
Ans: SQL_Latin1_General_CP1_CI_AS
2) How can I just capture the rows with the mixed case
SELECT * FROM dbo.TableName WHERE Column1 LIKE '%Test%' Collate SQL_Latin1_General_CP1_CS_AS
Most of the SQL Server installations are installed with the default collation which is case insensitive. This means that SQL Server ignores the case of the characters and treats the string 'Harish Kumar' equal to the string 'harish kumar'. If you need to differentiate these values and are unable to change the collation at the server, database or column level, how can you differentiate these values?
Answer
1) As you can see, this SQL Server has a case insensitive collation i.e. CI.
SELECT SERVERPROPERTY ('Collation')
Ans: SQL_Latin1_General_CP1_CI_AS
2) How can I just capture the rows with the mixed case
SELECT * FROM dbo.TableName WHERE Column1 LIKE '%Test%' Collate SQL_Latin1_General_CP1_CS_AS
No comments:
Post a Comment