Replace with wildcard, in SQL

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)ASBEGINDECLARE @nextColon intDECLARE @nextSlash intSET @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     ENDRETURN @breadcrumbEND

You could then execute it with:

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

This works for SQL Server 2005 and up.

create table strings (  string varchar(1000))insert into strings values( '/ID1:Category1/ID2:Category2/ID3:Category3/' )  insert into strings values( '/ID4:Category4/ID5:Category5/ID8:Category6/' )  insert into strings values( '/ID7:Category7/ID8:Category8/ID9:Category9/' )  gowith  replace_with_wildcard ( restrung ) as (   select replace( string, '', '' )   from strings  union all   select      replace( restrung, substring( restrung, patindex( '%ID%', restrung ), 4 ), '' )   from replace_with_wildcard   where patindex( '%ID%', restrung ) > 0 ) select restrungfrom replace_with_wildcard where charindex( ':', restrung ) = 0order by restrungdrop table strings 

You might be able to do this using a Split function. The following split function relies on the existence of a Numbers table which literally contains a sequential list of numbers like so:

Create Table dbo.Numbers( Value int not null primary key clustered )GOWith Nums As    (    Select ROW_NUMBER() OVER( Order By o.object_id ) As Num    From sys.objects as o        cross join sys.objects as o2    )Insert dbo.Numbers( Value )Select NumFrom NumsWhere Num Between 1 And 10000GO  Create Function [dbo].[udf_Split] (@DelimitedList nvarchar(max), @Delimiter nvarchar(2) = ',')Returns @SplitResults TABLE (Position int NOT NULL PRIMARY KEY, Value nvarchar(max))AS/*PURPOSE: to split the @DelimitedList based on the @DelimterDESIGN NOTES:    1. In general the contents of the next item is: NextDelimiterPosition - CurrentStartPosition    2. CurrentStartPosition =         CharIndex(@Delimiter, A.list, N.Value)  = Current Delimiter position        + Len(@Delimiter)                       + The number of delimiter characters         + 1                                     + 1 since the text of the item starts after the delimiter    3. We need to calculate the delimiter length because the LEN function excludes trailing spaces. Thus        if a delimiter of ", " (a comma followed by a space) is used, the LEN function will return 1.    4. The DataLength function returns the number of bytes in the string. However, since we're using        an nvarchar for the delimiter, the number of bytes will double the number of characters.*/Begin    Declare @DelimiterLength int    Set @DelimiterLength = DataLength(@Delimiter) / 2    If Left(@DelimitedList, @DelimiterLength) <> @Delimiter        Set @DelimitedList = @Delimiter + @DelimitedList    If Right(@DelimitedList, @DelimiterLength) <> @Delimiter        Set @DelimitedList = @DelimitedList + @Delimiter    Insert @SplitResults(Position, Value)    Select CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength                    , Substring (                    A.List                    , CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength                             , CharIndex(@Delimiter, A.list, N.Value + 1)                                                    - ( CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength )                     )    From dbo.Numbers As N        Cross Join (Select @DelimitedList As list) As A    Where N.Value > 0        And N.Value < LEN(A.list)        And Substring(A.list, N.Value, @DelimiterLength) = @Delimiter    Order By N.Value    ReturnEnd

You then might be able to run a query like so where you strip out the prefixes:

Select Table, Substring(S.Value, CharIndex(':', S.Value) + 1, Len(S.Value))From Table    Cross Apply dbo.udf_Split(Table.ListColumn, '/') As S

This would give you values like:

Category1Category2Category3

You could then use FOR XML PATH to combine them again:

Select Table.PK    ,   Stuff(  (                Select '/' + Substring(S.Value, CharIndex(':', S.Value) + 1, Len(S.Value))                From Table As Table1                    Cross Apply dbo.udf_Split(Table.ListColumn, '/') As S1                Where Table1.PK = Table.PK                Order By S1.Position                For Xml Path('')                ), 1, 1, '') As BreadCrumbFrom Table