I need to update single column for all the rows in a table. The update should happen in such way that data needs be filtered out by removing all text where braces are appended for the string and swap the strings separated by ','
.
Example:
x
Column A
---------
Ab,cde(123)
Ab,cde
yz,kol
yz,kol(567)
uv,xyz
first name,lastname (123456)
As per the above example, I need the final data to be something like this
Output
---------
cde, Ab
cde, Ab
kol, yz
kol, yz
xyz,uv
lastname,first name
Please let me know how we can achieve the above output in SQL Server.
Advertisement
Answer
Try this SQL Code:
;WITH cte
AS (
SELECT ColumnA AS [Column1]
,CASE
WHEN charindex('(', ColumnA) > 0
AND charindex(')', ColumnA) > charindex('(', ColumnA)
THEN stuff(ColumnA, charindex('(', ColumnA), charindex(')', ColumnA) - charindex('(', ColumnA) + 1, '')
ELSE ColumnA
END AS [Column2]
,0 AS [Level] FROM t1
)
,cte1
AS (
SELECT *
,row_number() OVER (
PARTITION BY [Column1] ORDER BY [Level] DESC
) AS Rn
FROM cte
)
SELECT Column1, Column2 ,
REPLACE( REPLACE(cte1.Column2 , SUBSTRING(Column2 , 0 , CHARINDEX(N',' , Column2)) , N'') , N',' , N'') + N', ' + SUBSTRING(Column2 , 0 , CHARINDEX(N',' , Column2)) AS OutPut
FROM cte1 where Rn = 1