Solved

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

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

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
Suppress if value zero or NULL in crystal report 2 43
What is needed to become a DBA? 7 56
mssql 7 32
Creating Scalar Function 3 19
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

738 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