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 '%; ;%'
timoteogaAsked:
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)) = '; ;'
0
 
JestersGrindCommented:
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

Greg
0
 
eridanixCommented:
The WHERE statement havent't to be here. But this is not solution.

Can you show example of datas in extended_desc fields?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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.
Sample-Data.xlsx
0
 
eridanixCommented:
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)
0
 
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

0
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.