Replace multiple characters in SQL

Question

I have a problem where I want to replace characters

I am using replace function but that is not giving desired output.

Values of column table_value needs to replaced with their fill names like

E - Email
P - Phone
M - Meeting

I am using this query

select table_value, 
       replace(replace(replace(table_value, 'M', 'MEETING'), 'E', 'EMAIL'), 'P', 'PHONE') required_value 
from foobar

so second required_value row should be EMAIL,PHONE,MEETING and so on.

What should I do so that required value is correct?

Answer 1

The below will work (even it's not a smart solution).

select 
    table_value, 
    replace(replace(replace(replace(table_value, 'M', 'MXXTING'), 'E', 'XMAIL'), 'P', 'PHONX'), 'X', 'E') required_value 
from foobar

Answer 2

You can do it using CTE to split the table values into E, P and M, then replace and put back together.

I assumed each record has a unique identifer Id but please replace that with whatever you have.

;WITH cte
AS
(
    SELECT Id, SUBSTRING(table_value, 1, 1) AS SingleValue, 1 AS ValueIndex
    FROM replacetable

    UNION ALL

    SELECT replacetable.Id, SUBSTRING(replacetable.table_value, cte.ValueIndex + 1, 1) AS SingleValue, cte.ValueIndex + 1 AS ValueIndex
    FROM cte
    INNER JOIN replacetable ON cte.ValueIndex < LEN(replacetable.table_value)
)

SELECT DISTINCT Id, 
    STUFF((SELECT DISTINCT ','+ CASE SingleValue 
            WHEN 'E' THEN 'EMAIL'
            WHEN 'P' THEN 'PHONE'
            WHEN 'M' THEN 'MEETING'
        END
       FROM cte c 
       WHERE c.Id = cte.Id 
       AND SingleValue <> ','
       FOR XML PATH ('')),1,1,'')
FROM cte

Answer 3

Sorry , for mess code, maybe this is not best way to solve this, but what I've tried:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE Function [dbo].[fn_CSVToTable] 
(
    @CSVList Varchar(max)
)
RETURNS @Table TABLE (ColumnData VARCHAR(100))
AS
BEGIN
    DECLARE @S varchar(max),
        @Split char(1),
        @X xml

    SELECT @Split = ','

    SELECT @X = CONVERT(xml,' <root> <s>' + REPLACE(@CSVList,@Split,'</s> <s>') + '</s>   </root> ')

    INSERT INTO @Table
    SELECT CASE  RTRIM(LTRIM(T.c.value('.','varchar(20)'))) WHEN 'M' THEN 'Meeting'
        WHEN 'P' THEN 'Phone'
        WHEN 'E' THEN 'Email'
        End

    FROM @X.nodes('/root/s') T(c)

    RETURN
END
GO

Then When I run this:

Select Main.table_value,
       Left(Main.ColumnData,Len(Main.ColumnData)-1) As ColumnData
From
    (
        Select distinct tt2.table_value, 
            (
                Select tt1.ColumnData+ ',' AS [text()]
                From (
                SELECT 
                    *
                    FROM  dbo.TestTable tt
                    CROSS APPLY dbo.fn_CSVToTable(tt.table_value)
                ) tt1
                Where tt1.table_value = tt2.TABLE_value
                ORDER BY tt1.table_value
                For XML PATH ('')
            ) ColumnData
        From dbo.TestTable tt2
    ) [Main]

I get this:

table_value                                        ColumnData
-------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
E,P                                                Email,Phone
E,P,M                                              Email,Phone,Meeting
P,E                                                Phone,Email
P,M                                                Phone,Meeting

(4 row(s) affected)

Answer 4

You could also use a DECODE or CASE to translate the values.