CODEWITHSUNDEEP
sql-serversql-server-2008
Venkat
Venkat

Reputation: 2156

String manipulation for a column in SQL Server

I have the following record in quotestable:

| quotesid | quotestags         |
+----------+--------------------+
| 123      | Friendsip, Courage |

I have the following query:

 update quotestable 
 set quotestags = COALESCE(quotestags, '') + ', Friendship' 
 where quotesid = 123

 update quotestable 
 set quotestags = COALESCE(quotestags, '') +', Courtesy' 
 where quotesid = 123

In the first query I am trying to append Friendship tag in the quotestag column but that value is already there. How do I check its duplicity? If the value is there donot add it. If the value is not there then add it.

Upvotes: 1

Views: 68

Answers (2)

user7715598
user7715598

Reputation:

Try this 

DEclare @Table TABLE( quotesid INT, quotestags VARCHAR(1000))
INSERT INTO @Table
SELECT 123 ,'Friendship, Courage' UNION ALL
SELECT 124 ,'Friendship, Courage' UNION ALL
SELECT 125 ,'Relationship, Courage' UNION ALL
SELECT 126 ,'Sincere Courage'

--SELECT quotesid,quotestags,CHARINDEX('Friendship',quotestags) from @Table

UPDATE t
SET quotestags=COALESCE(o.quotestags, '') + ', Friendship' 
from @Table t
INNER join @Table o
ON o.quotesid=t.quotesid
WHERE CHARINDEX('Friendship',t.quotestags)=0

SELECT * from @Table

Result

quotesid    quotestags
-----------------------------------------------
123         Friendship, Courage
124         Friendship, Courage
125         Relationship, Courage, Friendship
126         Sincere Courage, Friendship

Upvotes: 2

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can try something simple like below

update quotestable 
 set quotestags = COALESCE(quotestags, '') + ', Friendship' 
 where quotesid = 123 and NOT quotestags like '%Friendship%'

 update quotestable 
 set quotestags = COALESCE(quotestags, '') +', Courtesy' 
 where quotesid = 123 and NOT quotestags like '%Courtesy%'

Upvotes: 2

Related Questions