Skip to main content
Edited comment
Source Link
Shaulinator
  • 3.2k
  • 1
  • 13
  • 25

You can append a new collation to your select query to find case sensitive or insensitive.

-- Case sensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%'

-- Case insensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'

Just be aware of the performance problems this could present. You will need to scan the clustered index to adjust / find the values when you perform the collation. The way you are writing the LIKE piece also makes the query non-sargable.

I picked up the collation trick from Kendra Little's SELECT Seminar classes. You can find additional collation information though from Ben Snaidero from MS SQL Tips.

MSDN on Collate.

You can append a new collation to your select query to find case sensitive or insensitive.

-- Case sensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%'

- Case insensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'

Just be aware of the performance problems this could present. You will need to scan the clustered index to adjust / find the values when you perform the collation. The way you are writing the LIKE piece also makes the query non-sargable.

I picked up the collation trick from Kendra Little's SELECT Seminar classes. You can find additional collation information though from Ben Snaidero from MS SQL Tips.

MSDN on Collate.

You can append a new collation to your select query to find case sensitive or insensitive.

-- Case sensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%'

-- Case insensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'

Just be aware of the performance problems this could present. You will need to scan the clustered index to adjust / find the values when you perform the collation. The way you are writing the LIKE piece also makes the query non-sargable.

I picked up the collation trick from Kendra Little's SELECT Seminar classes. You can find additional collation information though from Ben Snaidero from MS SQL Tips.

MSDN on Collate.

Added more information and resources.
Source Link
Shaulinator
  • 3.2k
  • 1
  • 13
  • 25

You can append a new collation to your select query to find case sensitive or insensitive.

-- Case sensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%' 

- Case insensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'

Just be aware of the performance problems this could present. You will need to scan the clustered index to adjust / find the values when you perform the collation. The way you are writing the LIKE piece also makes the query non-sargable.

I picked up the collation trick from Kendra Little's SELECT Seminar classes. You can find additional collation information though from Ben Snaidero from MS SQL Tips.

MSDN on Collate.

You can append a new collation to your select query to find case sensitive or insensitive.

SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%'

SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'

You can append a new collation to your select query to find case sensitive or insensitive.

-- Case sensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%' 

- Case insensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'

Just be aware of the performance problems this could present. You will need to scan the clustered index to adjust / find the values when you perform the collation. The way you are writing the LIKE piece also makes the query non-sargable.

I picked up the collation trick from Kendra Little's SELECT Seminar classes. You can find additional collation information though from Ben Snaidero from MS SQL Tips.

MSDN on Collate.

Source Link
Shaulinator
  • 3.2k
  • 1
  • 13
  • 25

You can append a new collation to your select query to find case sensitive or insensitive.

SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%'

SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'