?
Solved

SQL Server Replace Syntax for replacing text string with syntax

Posted on 2011-09-27
6
Medium Priority
?
217 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

777 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