Remove Second Appearence Of A Substring From String In Sql Server

I need to remove the second appearance of a substring from the main string, IF both substrings are next to each other. e.g.: Jhon\Jhon\Jane\Mary\Bob needs to end Jhon\Jane\Mary\Bob

Solution 1:

So here is one try, but as I said, I don't think you will get a fast solution in native T-SQL.

First, if you don't already have a numbers table, create one:

SET NOCOUNT ON;
DECLARE@UpperLimitINT;
SET@UpperLimit=4000;

WITH n AS
(
    SELECT
        rn =ROW_NUMBER() OVER
        (ORDERBY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSSJOIN sys.all_objects AS s2
)
SELECT [Number] = rn -1INTO dbo.Numbers FROM n
WHERE rn <=@UpperLimit+1;

CREATEUNIQUE CLUSTERED INDEX n ON dbo.Numbers([Number]);

Then create two functions. One that splits strings apart into a table, and then another that re-joins the results of the first function but ignores any subsequent duplicates.

CREATEFUNCTIONdbo.SplitStrings
(
    @List NVARCHAR(4000),
    @Delim CHAR(1)
)
RETURNSTABLEASRETURN ( SELECT 
      rn = ROW_NUMBER() OVER (ORDER BY CHARINDEX(@Delim, @List + @Delim)),
      [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
      CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
      FROM dbo.Numbers
      WHERE Number <= LEN(@List)
      AND SUBSTRING(@Delim + @List, [Number], 1) = @Delim
    );
GO

Second function:

CREATEFUNCTIONdbo.RebuildString
(
    @List NVARCHAR(4000),
    @Delim CHAR(1)
)
RETURNSNVARCHAR(4000)
ASBEGINRETURN ( SELECT newval = STUFF((
     SELECT @Delim + x.[Value] FROM dbo.SplitStrings(@List, @Delim) AS x
      LEFT OUTER JOIN dbo.SplitStrings(@List, @Delim) AS x2
      ON x.rn = x2.rn + 1
      WHERE (x2.rn IS NULL OR x.value <> x2.value)
      ORDER BY x.rn
      FOR XML PATH, TYPE).value('.', 'nvarchar(max)'), 1, 1, '')
    );
ENDGO

Now you can try it against the two samples you gave in your question:

;WITH cte(colname) AS
(
    SELECT'Jhon\Jhon\Jane\Mary\Bob'UNIONALLSELECT'Mary\Jane\Mary\Bob'
)
SELECT dbo.RebuildString(colname, '\')
FROM cte;

Results:

Jhon\Jane\Mary\Bob
Mary\Jane\Mary\Bob

But I strongly, strongly, strongly recommend you thoroughly test this against your typical data size before deciding to use it.

Solution 2:

I decided to go for string manipulation. I thought it'd take longer to execute the query, but testing it in... ejem... production environment... ejem... I found out that it did not (much to my surprise). It ain't pretty, I know, but it's easy to mantain...

Here is a simplified version of my final query:

SELECT SOQ.PracticeId,
       CASEWHENLEFT(SOQ.myString, SOQ.SlashPos) =SUBSTRING(SOQ.myString, SOQ.SlashPos +1, LEN(LEFT(SOQ.myString, SOQ.SlashPos)))
            THENRIGHT(SOQ.myString, LEN(SOQ.myString) - SOQ.SlashPos)
            ELSE SOQ.myString
       ENDas myString
  FROM (SELECT OQ.AllFields, OQ.myString, CHARINDEX('\', OQ.myString, 0) as SlashPos
          FROM MyOriginalQuery OQ) SOQ