Solved

SQL Server Replace Syntax for replacing text string with syntax

Posted on 2011-09-27
6
212 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 500 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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 …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now