Click here to Skip to main content
15,843,623 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
In sql server i have a colum of string like

have a table like this

XML
Value   String
------------------------------------------------------------------------
1       Fruits - Apple, orange, Mango ; Flowers - Rose, Lilly, Lotous
2       Fruits - orange, Mango ; Flowers - Rose, Lotous
3       Fruits - Apple, orange, Mango
4       Fruits - Apple, orange, Mango ; Flowers - Rose


I want to convert the above column like below

XML
Value   String
------------------------------------------------------------------------
1       Apple, orange, Mango, Rose, Lilly, Lotous
2       orange, Mango, Rose, Lotous
3       Apple, orange, Mango
4       Apple, orange, Mango, Rose



Thanks in advance
Please let me know how to proceed with this.
Posted
Updated 2-Aug-13 3:09am
v3

All what you need to do is to use REPLACE[^] function ;)

Have a look at below example:
SQL
DECLARE @tbl TABLE ([Value] INT, String VARCHAR(MAX))

INSERT INTO @tbl ([Value], String)
SELECT 1, 'Fruits - Apple, orange, Mango ; Flowers - Rose, Lilly, Lotous'
UNION ALL SELECT 2, 'Fruits - orange, Mango ; Flowers - Rose, Lotous'
UNION ALL SELECT 3, 'Fruits - Apple, orange, Mango'
UNION ALL SELECT 4, 'Fruits - Apple, orange, Mango ; Flowers - Rose'

SELECT [Value], REPLACE(REPLACE([String], 'Fruits - ',''), ' ; Flowers - ', ', ') AS String
FROM @tbl


Result:
1   Apple, orange, Mango, Rose, Lilly, Lotous
2   orange, Mango, Rose, Lotous
3   Apple, orange, Mango
4   Apple, orange, Mango, Rose
 
Share this answer
 
Try this

SQL
update tableName
set ColumnName= replace(ColumnName, 'Fruits -', '')


or if just want to select

select  replace(ColumnName, 'Fruits -', '') from TableName
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900