Patindex Case Sensitive Search

Patindex Case Sensitive Search

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.

Thanks to Jeff Moden I found that I can 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
 
Hope  this article may help others looking for case sensitive search solution in SQL Server.

See Also


Leave a Comment
  • Please add 1 and 1 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Naomi  N edited Revision 3. Comment: Added See Also

  • Naomi  N edited Revision 2. Comment: Formatting

  • Naomi  N edited Revision 1. Comment: Typo fix

Page 1 of 1 (3 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Naomi  N edited Revision 1. Comment: Typo fix

  • Tip type of the article

  • Naomi  N edited Revision 2. Comment: Formatting

  • Naomi  N edited Revision 3. Comment: Added See Also

Page 1 of 1 (4 items)