Solved

replacing multiple types of substring with a single substring in MS SQL 2005

Posted on 2014-02-24
4
347 Views
Last Modified: 2014-03-10
Hi

 I wanted to replace any of the below variations  in a string with the single sting RPT. How can I do it in one line?

Any number immediately following a "RPT#<n> -" should be replaced with RPT#<n>. We can assume <n> is a single digit.

string = 'Adam reports RPT#2-XYZ' should look like Adam reports RPT#2XYZ
string = 'Adam reports RPT#3-ABC' should look like Adam reports RPT#3ABC

As the "-" occurs else where in the longer strings I can not simply replace "-" with "".
0
Comment
Question by:LuckyLucks
[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
  • 2
4 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39884370
Ok, so maybe not overly graceful, but as long as the fixed string is "RPT#x-" and you need to get rid of the "-", and the string only appears once ....

This appears to work.

update table1
set col1 = stuff(col1, charindex('RPT#', col1)+5, 1, '') 
where col1 like '%RPT#_-%'

Open in new window


It specifically searches for the starting position of 'RPT#' - adds 5 bytes (since the "-" is 5 characters after the R) and replaces it with nothing.

Wouldn't use it all the time, but if it's a one off data fix, it might just do the trick - however review your expected outcome carefully.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39884404
One minor enhancement to insure accuracy with the stated requirements -- NO pts for me just for this please.


where col1 like '%RPT#[0-9][-]%'
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39884524
Any chance that the number following RPT will have more than one digit?  If so, Scott's suggestion may not always work.

Is it true that the sequence <digit><hyphen><letter> only appears in instances where you would want to remove the hyphen?  If so, you may want to go with:

where col1 like '%[0-9][-][A-Z]%'

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39885727
>> Any chance that the number following RPT will have more than one digit?  If so, Scott's suggestion may not always work. <<

The original pattern from Steve also only allowed for a single character.  The main difference is that I limited that character to being a digit (0-9).
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

622 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