Replace with wildcard, in SQL

Question

I know MS T-SQL does not support regular expression, but I need similar functionality. Here's what I'm trying to do:

I have a varchar table field which stores a breadcrumb, like this:

/ID1:Category1/ID2:Category2/ID3:Category3/

Each Category name is preceded by its Category ID, separated by a colon. I'd like to select and display these breadcrumbs but I want to remove the Category IDs and colons, like this:

/Category1/Category2/Category3/

Everything between the leading slash (/) up to and including the colon (:) should be stripped out.

I don't have the option of extracting the data, manipulating it externally, and re-inserting back into the table; so I'm trying to accomplish this in a SELECT statement.

I also can't resort to using a cursor to loop through each row and clean each field with a nested loop, due to the number of rows returned in the SELECT.

Can this be done?

Thanks all - Jay

Answer 1

I think your best bet is going to be to use a recursive user-defined function (UDF). I've included some code here that you can use to pass in a string to achieve the results you're looking for.

CREATE FUNCTION ufn_StripIDsFromBreadcrumb (@cIndex int, @breadcrumb varchar(max), @theString varchar(max))

RETURNS varchar(max)

AS

BEGIN
DECLARE @nextColon int
DECLARE @nextSlash int

SET @nextColon = CHARINDEX(':', @theString, @cIndex)
SET @nextSlash = CHARINDEX('/', @theString, @nextColon)
SET @breadcrumb = @breadcrumb + SUBSTRING(@theString, @nextColon + 1, @nextSlash - @nextColon)

IF @nextSlash != LEN(@theString)

     BEGIN
     exec @breadcrumb = ufn_StripIDsFromBreadcrumb @cIndex =  @nextSlash, @breadcrumb = @breadcrumb, @theString = @theString
     END
RETURN @breadcrumb
END

---

You could then execute it with:

DECLARE @myString varchar(max)
EXEC @myString = ufn_StripIDsFromBreadcrumb 1, '/', '/ID1:Category1/ID2:Category2/ID3:Category3/'
PRINT @myString