Solved

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

Posted on 2014-02-24
4
337 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 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Do I need CLUSTERED here? 13 45
sql server tables from access 18 22
T-SQL:  Collapsing 9 25
syntax sql error 2 14
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

810 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