Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-02-24
4
Medium Priority
?
353 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
  • 2
4 Comments
 
LVL 23

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 70

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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 70

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

564 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