SQL Server Parsing Strings Based on Right Side Characters

I’m currently working on a project that requires extracting data based on URL’s. However the entire url is not needed only the text to the far left hand side of the last forward slash “/”. For example below I want to exclude the junk string.

/level1/level2/level3/junk.

Since the levels can vary I need a parser that will work from the right hand side backwards. SQL Server lacks the functionality to perform this. This is not a bad thing since string manipulation should be done outside the DB in some form a managed code, and giving developers this functionality, would only lead to headaches and expenses.

To circumnavigate the lack of functionality I wrote a very straight forward CLR to perform the parsing. Notice I’ve got two separate functions in the code, one that grabs the last value, and one that excludes it. Once registered with SQL, either can be used.

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System;


public partial class CustomStringFunctions
{
[SqlFunction()]
public static SqlString leftValueIncludeIndexChar(string originalString, string charVal)
{
int substringEnd;
string finalData;
int charValLength;

charValLength = charVal.Length;
substringEnd = originalString.LastIndexOf(charVal);
finalData = originalString.Substring(0, substringEnd + charValLength);
return finalData;

}

public static SqlString leftValueExcludeIndexChar(string originalString, string charVal)
{
int substringEnd;
string finalData;

substringEnd = originalString.LastIndexOf(charVal);
finalData = originalString.Substring(0, substringEnd);
return finalData;

}
}

Once the assembly is compiled Register it and insert it into a function. Below I created two functions, one to include the characteers and one to exclude.


Create assembly LastCharIndex
from 'c:\testing\lastindex.dll'
go
Create function LastCharIndexInclude(@InputString nvarchar(500), @Criteria nvarchar(50))
Returns nvarchar(500) External Name LastCharIndex.CustomStringFunctions.leftValueIncludeIndexChar

Create function LastCharIndexExclude(@InputString nvarchar(500), @Criteria nvarchar(50))
Returns nvarchar(500) External Name LastCharIndex.CustomStringFunctions.leftValueExcludeIndexChar



Examples:

select dbo.LastCharIndexInclude('/level1/level2/level3/junk', '/')
select dbo.LastCharIndexExclude('/level1/level2/level3/junk', '/')