Patindex Case Sensitive Search

  • Article

PATINDEX case-sensitive search on case-insensitive column

 
 
This article is a result of a quick research of the problem of using PATINDEX to search case insensitive column using case sensitive search. The BOL does not show examples of how to implement particular collation with the PATINDEX function. A relevant thread in MSDN Transact-SQL forum showed the syntax.

 Jeff Moden suggested to use Binary collation to be able to use ranges in the search.

So, if we want to split proper names such as JohnDoe, EdgarPo, etc. into two parts, we can use the following code:

DECLARE @t TABLE (Col VARCHAR(20))
 
INSERT INTO  @t
 
SELECT 'JohnDoe'
 
UNION ALL
 
SELECT 'AvramLincoln'
 
UNION ALL
  
SELECT 'Brad Pitt'
 
SELECT Col
 
       ,COALESCE(STUFF(col, NULLIF(patindex('%[a-z][A-Z]%', Col COLLATE  Latin1_General_BIN), 0) + 1, 0, ' '), Col) AS  NewCol
 
FROM @t

  Case-sensitive REPLACE on the case-insensitive column 

In order to use case-sensitive REPLACE command, we need to use COLLATE keyword on each argument of the command. The following sample shows how to replace low case 'a' with a space:

IF OBJECT_ID('tempdb..#tcasetest') IS  NOT NULL
    DROP TABLE  #tcasetest
 
CREATE TABLE  #tcasetest (
    id INT
    ,value VARCHAR(100)
    )
 
DECLARE @val CHAR(1)
 
SET @val = 'a'
 
INSERT INTO  #tcasetest (
    id
    ,value
    )
SELECT 1
    ,'A,X,T,Y,y,t,x,G,g,a,A,b,B'
 
UPDATE #tcasetest
SET value = replace(value COLLATE  Latin1_General_BIN, @val COLLATE Latin1_General_BIN, ' ' COLLATE  Latin1_General_BIN)
 
SELECT *
FROM #tcasetest

Hope this article may help others looking for case sensitive search and replace solutions in SQL Server.

See Also