Solved

SQL - substring query assistance

Posted on 2015-01-16
3
111 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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 …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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