Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server Replace Syntax for replacing text string with syntax

Posted on 2011-09-27
6
Medium Priority
?
219 Views
Last Modified: 2012-05-12
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 '%; ;%'
0
Comment
Question by:timoteoga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36712844
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
 
LVL 5

Expert Comment

by:eridanix
ID: 36712864
The WHERE statement havent't to be here. But this is not solution.

Can you show example of datas in extended_desc fields?
0
 

Author Comment

by:timoteoga
ID: 36713285
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 5

Expert Comment

by:eridanix
ID: 36713315
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
 
LVL 5

Accepted Solution

by:
eridanix earned 2000 total points
ID: 36713395
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36714885
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question