how to query that stores mulit select field data
-
Hi everyone,
I have a table as below, I would like to strip out the commas in the oppo_SCRMcompetitor column .
Can someone help please. I tried the substring but that did not work out quite well.
Thanks in advance for the help.
-
If you always want to replace every comma, you can do this:
UPDATE dbo.table_name
SET oppo_SCRMcompetitor = REPLACE(oppo_SCRMcompetitor, ',', '')
WHERE oppo_SCRMcompetitor LIKE '%,%'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
If you only want to replace leading and/or trailing commas, then this:
UPDATE tn
SET oppo_SCRMcompetitor = CASE WHEN LEFT(oppo_SCRMcompetitor_ca1, 1) = ','
THEN STUFF(oppo_SCRMcompetitor_ca1, 1, 1, '')
ELSE oppo_SCRMcompetitor_ca1 END
FROM dbo.table_name tn
CROSS APPLY (
SELECT CASE WHEN RIGHT(oppo_SCRMcompetitor, 1) = ','
THEN STUFF(oppo_SCRMcompetitor, LEN(oppo_SCRMcompetitor), 1, 1, '')
ELSE oppo_SCRMcompetitor END AS oppo_SCRMcompetitor_ca1
) AS ca1
WHERE oppo_SCRMcompetitor LIKE ',%' OR oppo_SCRMcompetitor LIKE '%,'SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
I want this for building a report. Thanks Scott, I cannot run the update statement.
-
I got the idea from Scotts STUFF function and did this and it was enough for the report -
select Oppo_OpportunityId,Oppo_Description,STUFF((STUFF(oppo_SCRMcompetitor,1,1,'')),LEN(oppo_SCRMcompetitor)-1,1,'') from Opportunity where oppo_SCRMcompetitor
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply