How to Replace After special characters with other column value in same position in sql?

Question

How to replace the missing string in between special characters with other column same position special character string in sql? below is the example

Eg :

ColumnA                columnB        Output of columnb 
~as~df~gf~er            ~qw~~~          ~qw~df~gf~er
~Evening~Afternoon       ~~             ~Evening~Afternoon
~qw~dg~er~rt~yu~io~ty   ~df~~rt~~we~~   ~df~dg~rt~rt~we~io~ty

Note: Both columns special characters count will be same. I need output in column like this ~qw~df~gf~er .
I want change dynamically in all the missing strings in special character.Please help me on this

Where ever missing string in between special character we need to replace the columnA string in same position.Out of columnb is need to update columnb like this ( am giving sample how I need to update in columnb ) — Malyadri, Oct 09 '17 at 10:41

score 0 · Answer 1 · answered Oct 09 '17 at 11:35

You need the right functions and this can be solve easily. For example, in my database I have:

DECLARE @DataSource TABLE
(   
    [ColumnA] VARCHAR(1024)
   ,[ColumnB] VARCHAR(1024)
);

INSERT INTO @DataSource ([ColumnA], [ColumnB])
VALUES ('~as~df~gf~er', '~qw~~~')
      ,('~Evening~Afternoon', '~~')
      ,('~qw~dg~er~rt~yu~io~ty', '~df~~rt~~we~~');


SELECT DS.[ColumnA]
      ,DS.[ColumnB]
      ,[dbo].[ConcatenateWithOrderAndDelimiter] (A.[index], ISNULL(NULLIF(B.[value], ''), A.[value]), '~')
FROM @DataSource DS
CROSS APPLY [dbo].[fn_Utils_RegexSplitWithOrder] ([ColumnA], '~') A
LEFT JOIN
(
    SELECT [ColumnA]
          ,B.[index]
          ,B.[value]
    FROM @DataSource DS
    CROSS APPLY [dbo].[fn_Utils_RegexSplitWithOrder] ([ColumnB], '~') B
) B
    ON DS.[ColumnA] = B.[ColumnA]
    AND A.[index] = B.[index]
GROUP BY DS.[ColumnA]
        ,DS.[ColumnB];

So, basically you need two functions:

one for splitting strings by value
one for string aggregation (concatenation)

In my case I am using SQL CLR function - you can find more about them here.

In SQL Server 2017, we can use STRING_AGG and from SQL Server 2016 we can use STRING_SPIT.

If you do not want to waste time for implementing SQL CLR functions, you can add a split function (there are a lot of them in the net). For example, I have used this one:

CREATE FUNCTION [dbo].[fn_Analysis_ConvertCsvListToNVarCharTableWithOrder](@List nvarchar(max), @Delimiter nvarchar(10) = ',')
RETURNS @result TABLE 
(   
    [Value] nvarchar(max),
    [SortOrder] bigint NOT NULL
)
AS
BEGIN
    IF @Delimiter is null 
    BEGIN
        SET @Delimiter = ','
    END
    DECLARE @XML xml = N'<r><![CDATA[' + REPLACE(@List, @Delimiter, ']]></r><r><![CDATA[') + ']]></r>'

    DECLARE @BufTable TABLE (Value nvarchar(max), SortOrder bigint NOT NULL IDENTITY(1, 1) PRIMARY KEY)
    INSERT INTO @BufTable (Value)
        SELECT Tbl.Col.value('.', 'nvarchar(max)')
        FROM   @xml.nodes('//r') Tbl(Col)
        OPTION (OPTIMIZE FOR (@xml = NULL)) 

    INSERT INTO @result (Value, SortOrder)
        SELECT Value, SortOrder
        FROM @BufTable

    RETURN
END

And this should will give you want you want:

WITH DataSource AS
(
    SELECT DS.[ColumnA]
          ,DS.[ColumnB]
          ,A.[SortOrder]
          ,ISNULL(NULLIF(B.[value], ''), A.[Value]) AS [Value]
    FROM @DataSource DS
    CROSS APPLY [dbo].[fn_Analysis_ConvertCsvListToNVarCharTableWithOrder] (REPLACE([ColumnA], ',', '~'), '~') A
    LEFT JOIN
    (
        SELECT [ColumnA]
              ,B.[SortOrder]
              ,B.[value]
        FROM @DataSource DS
        CROSS APPLY [dbo].[fn_Analysis_ConvertCsvListToNVarCharTableWithOrder] (REPLACE([ColumnB], ',', '~'), '~') B
    ) B
        ON DS.[ColumnA] = B.[ColumnA]
        AND A.[SortOrder] = B.[SortOrder]
)
SELECT DISTINCT A.[ColumnA]
               ,A.[ColumnB]
               ,DS.[value]
FROM DataSource A
CROSS APPLY
(
    SELECT STUFF
    (
        (
            SELECT '~' + B.[Value]
            FROM DataSource B
            WHERE A.[ColumnA]  = B.[ColumnA]
            ORDER BY B.[SortOrder]
            FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)')
        ,1
        ,1
        ,''
    )
) DS ([value]);

Yogesh Sharma · Answer 2 · 2017-10-09T14:12:38.183

You could try without creating Function() :-

DECLARE @ColumnA NVARCHAR(MAX);
DECLARE @columnB NVARCHAR(MAX);

SET @ColumnA = '~as~df~gf~er ';
SET @columnB = ' ~qw~~~';

;WITH CTE
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA,
            ROW_NUMBER() OVER(ORDER BY @ColumnA) RN
     FROM
     (
         SELECT CAST('<M>'+REPLACE(@ColumnA, '~', '</M><M>')+'</M>' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/M') AS Split(a)),
     CTE1
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA1,
            ROW_NUMBER() OVER(ORDER BY @columnB) RN
     FROM
     (
         SELECT CAST('<M>'+REPLACE(@columnB, '~', '</M><M>')+'</M>' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/M') AS Split(a))
     SELECT @ColumnA AS ColumnA,
            @columnB AS columnB,
            [Output of columnb] =
     (
         SELECT '~'+CASE
                        WHEN C1.DATA1 = ''
                        THEN C2.DATA
                        ELSE C1.DATA1
                    END
         FROM CTE1 C1
              INNER JOIN CTE C2 ON C2.RN = C1.RN
                                   AND C1.RN > 1 FOR XML PATH('')
     );

Desired Output :

ColumnA                columnB        Output of columnb 
~as~df~gf~er            ~qw~~~          ~qw~df~gf~er

How to Replace After special characters with other column value in same position in sql?

2 Answers2