Replace with wildcard, in SQL
- by Jay
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
