Solved

SQL - substring query assistance

Posted on 2015-01-16
3
110 Views
Last Modified: 2015-01-19
Hi experts,

I am trying to write a query that replaces the very first instance of the word 'Cath' in a string, with the word 'Echo'.

So far I have:

  update  card_proceduresextpop_
set reportdisplay =  REPLACE(SUBSTRING(reportdisplay, 1, CHARINDEX(reportdisplay,'Echo',1)), 'Echo', 'Cath')
         , SUBSTRING(reportdisplay, CHARINDEX(reportdisplay,'Echo',1)+1, LEN(reportdisplay))
  FROM card_proceduresextpop_
  where procedureabbr = 'Echo'
and substring(reportdisplay, 1, 4) = 'Echo'

but it gives me an error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ','.


Any ideas?

Thanks
0
Comment
Question by:robthomas09
[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
3 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 400 total points
ID: 40554533
--replaces the very first instance of the word 'Cath' in a string, with the word 'Echo'
--....that's what I did, although your code does the opposite...
--...swap 'Cath' for 'Echo' and vice-versa in the string below if you need to

update  card_proceduresextpop_
set reportdisplay =  stuff(reportdisplay, CHARINDEX('Cath', reportdisplay), 4, 'Echo')
where reportdisplay like '%Cath%'
0
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 100 total points
ID: 40554940
Hi,

You can try this logic

DECLARE @reportdisplay VARCHAR(40) = 'HELLO Cath Cath'

SELECT STUFF(@reportdisplay,CHARINDEX('Cath',@reportdisplay,1),LEN('Cath'),'Echo')

Open in new window

0
 

Author Closing Comment

by:robthomas09
ID: 40558139
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

732 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