Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by (eMicrosoft Partne)
  • When: 11 Jan 2013 9:18 AM
  • Last revision by (eMicrosoft Partne)
  • When: 4 Sep 2013 2:47 PM
  • Revisions: 5
  • Comments: 4
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  Patindex Case Sensitive Search

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

, , , , , [Edit tags]
Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 4 Sep 2013 2:47 PM

    Naomi  N edited Revision 3. Comment: Added See Also

  • 9 Aug 2013 3:58 PM

    Naomi  N edited Revision 2. Comment: Formatting

  • 2 Jun 2013 6:51 PM

    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
  • Posted by
    on 2 Jun 2013 6:51 PM

    Naomi  N edited Revision 1. Comment: Typo fix

  • Posted by
    on 7 Jul 2013 2:51 PM

    Tip type of the article

  • Posted by
    on 9 Aug 2013 3:58 PM

    Naomi  N edited Revision 2. Comment: Formatting

  • Posted by
    on 4 Sep 2013 2:47 PM

    Naomi  N edited Revision 3. Comment: Added See Also

Page 1 of 1 (4 items)