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
Solved

SQL - substring query assistance

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

Suggested Solutions

Title # Comments Views Activity
Downgrading MS SQL 2008 R2 Enterprise to MS SQL 2005 Standard? 12 64
date diff with Fiscal Calendar 4 53
SubQuery link 4 35
SQL - Simple Pivot query 8 11
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 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