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?
If you wanted to replace the words with blank string, go with REGEXP_REPLACE() . If you want to replace the words with other words, for example replacing & with and then use replace() . If there are multiple words to be replaced, use multiple nested replace() .
Using the REPLACE() function will allow you to change a single character or multiple values within a string, whether working to SELECT or UPDATE data.
SQL Server REPLACE() Function The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.
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
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
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With