Pages - Menu

SQL - How to do a CHARINDEX in reverse

I am a little surprise when SQL 2012 have introduced functions like TRY_CONVERT, but there are still no built-in functions to perform a CHARINDEX in reverse.

Code

Select UrlRecord.Slug
, LEN(UrlRecord.Slug) [Length]
, CHARINDEX('-', UrlRecord.Slug, 0) [Normal CHARINDEX]
, LEN(UrlRecord.Slug) - CHARINDEX('-', Reverse(UrlRecord.Slug), 0) + 1 [Reverse CHARINDEX with bug]
, IIF(CHARINDEX('-', UrlRecord.Slug, 0) = 0, 0, LEN(UrlRecord.Slug) - CHARINDEX('-', Reverse(UrlRecord.Slug), 0) + 1) [Desired CHARINDEX]
from UrlRecord
where EntityName = 'Category'
Order by Slug



In the above code, we are trying to find the last position (a charindex in reverse order) of a dash '-' from a string (UrlRecord.Slug).


  • Row 68, for a string that have only one matching character '-', either direction will return the same result.
  • Row 72, a Reverse CharIndex will return 10 for the last occurrence as opposed to a CharIndex that will return 5 for the first occurrence.
  • Row 77, there is a bug when we calculate the reverse but no matching string, it will return the full length. We will do a IIF check to return 0 when there is no match.


The final code became this.

Declare @matchChar varchar(1) = '-'
Declare @sourceString varchar(max) = 'test-test-test'

Select IIF(CHARINDEX(@matchChar, @sourceString, 0) = 0, 0, LEN(@sourceString) - CHARINDEX(@matchChar, Reverse(@sourceString), 0) + 1)

Performance

I did not pay attention to performance at all. I am sure there are rooms for improvement, but I am thinking a built-in function for such a simple operation is probably overdue by Microsoft anyway.

No comments:

Post a comment