SQL Server Replace Syntax for replacing text string with syntax

I'm trying the following - basically just trying to remove any occurrence of that '; ;' and replace it with blank or nothing.  What am I missing in the syntax?  Every time I run this, it just wipes the whole extended_desc field and makes the whole field null, rather than just wiping out those three characters...

UPDATE inv_mast
SET    extended_desc = replace(extended_desc,'; ;','')
WHERE  extended_desc LIKE '%; ;%'
Who is Participating?
eridanixConnect With a Mentor Commented:
Better, if there are any records, where '; ;' is not at the end, than add WHERE statement:

UPDATE inv_mast
SET extended_desc = SUBSTRING(extended_desc , 0, LEN(extended_desc) - 2)
WHERE SUBSTRING(extended_desc, LEN(extended_desc ) - 2, LEN(extended_desc)) = '; ;'
Your update looks fine.  The only time replace returns null is if one of the arguments is null, which shouldn't be the case here.  Did you run this as a select to see how many rows are affected?

SELECT * FROM inv_mast WHERE extended_desc LIKE '%; ;%'

Also, does this return any records?

SELECT * FROM inv_mast WHERE extended_desc IS NULL

The WHERE statement havent't to be here. But this is not solution.

Can you show example of datas in extended_desc fields?
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

timoteogaAuthor Commented:
Definitely returns records - I did several different variations and every time the update updated the exact number of rows I had seen in the select results.  Attached is some sample data.  I realize that it doesn't fit the where clause I mentioned above - this is just a variation of the where clause.
The string '; ;' is only on end of record, so you can use this query

UPDATE inv_mast
SET extended_desc = SUBSTRING(extended_desc , 0, LEN(extended_desc) - 2)
Bhavesh ShahLead AnalysistCommented:
Hi Author,

Your query is right.

It should work.

select replace('DOUBLE ACTING; DOUBLE END OR REAR PIVOT MOUNT; 5/16 INCH BORE; STROKE: 0.5 INCH(S); ;','; ;','')

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.