Is there a better way to do multiple replace on a string?

Question

I have a varchar column which has a variation of the following string:

Stations, FlightName, FlightWeeks, BindTo, SpotLength, foo, bar, etc...

I need to change it to Stations to Station, FlightName to Flight Name, BindTo to Bind To etc to eventually come out like this:

Station; Flight Name; Weeks; Bind To; Spot Length; foo; bar etc

I have a really ugly code to do this:

 select replace(replace(replace(replace(replace(replace(GroupFlightsBy, ', ', ';'), 
'Stations', 'Station'), 'FlightName', 'Flight Name'), 'FlightWeeks', 'Weeks'), 'BindTo', 'Bind To'), 
'SpotLength', 'Spot Length')

Is there a better way of doing this, yet as performant?

First thought is not about the string replaces; but it sounds like you're merged data in a varchar-column instead of using separate columns. What does these values mean? — sisve, Mar 03 '15 at 07:10
@SimonSvensson Basically those are values from the multi-select dropdown box. The data is already there - I can't change it. — AngryHacker, Mar 03 '15 at 07:21
Why can't you change the database? Is it stored on some read-only media? Or are you saying that you, as a developer and assumed to know best during development, don't have the permissions to do changes? (Leading question; a database can always be changed; the no-change requirement is usually imposed from somewhere human-ish.) — sisve, Mar 03 '15 at 07:40
@AngryHacker so you are going to deal with fixed number of data (like Stations, FlightName etc), right? — SelvaS, Mar 03 '15 at 07:47
@SimonSvensson Because this system is in maintenance mode. I don't think I want to risk re-architecting the system just so that my code looks a bit prettier. — AngryHacker, Mar 03 '15 at 08:00

score 1 · Answer 1 · answered Mar 03 '15 at 07:23

1

You could write your own Transformation-Function in TSQL but i think it will not be as performant as a multi-replace. You should avoid using your multi-replace in WHERE/ORDER-clauses

answered Mar 03 '15 at 07:23

swe

1,416
16
26

score 0 · Accepted Answer · edited May 23 '17 at 11:56

Creating CLR function is a solution for calling multiple REPLACE functions by replacing a single function. Yes there are some performance considerations using CLR functions but if you are dealing with low number of data, then it would be fine.

For creating CLR functions,

For your requirement I have created a sample function like this,

using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString ReplaceFunc(string inputString)
    {
        return new SqlString (inputString
            .Replace("Stations", "Station")
            .Replace("FlightName", "Flight Name")
            .Replace("FlightWeeks", "Weeks")
            .Replace("BindTo", "Bind To")
            .Replace("SpotLength", "Spot Length")
            .Replace(", ", ";"));
    }
};

and called from SQL like this,

DECLARE @GroupFlightsBy VARCHAR(MAX) = 'Stations, FlightName, FlightWeeks, BindTo, 
SpotLength, foo, bar'

SELECT dbo.ReplaceFunc(@GroupFlightsBy)

For more about CLR functions,

http://sqlmag.com/database-development/common-language-runtime-dba-point-view

https://stackoverflow.com/a/25876797/311255

sure you can do it via clr. but a multireplace can also be done by a tsql-function, which would be easier to maintain and faster in execution. so why is this the solution? — swe, Mar 04 '15 at 08:13

score 0 · Answer 3 · answered Mar 05 '15 at 06:08

I like CLR idea. Also the requirement is not very systematic.like FlightWeeks become Weeks.Why ?

Try my script,it work fine except FlightWeeks

DECLARE @Temp VARCHAR(100)='FlightName'

Declare @Pattern1 as varchar(50)
Declare @Pattern2 as varchar(50)='%s%'
Declare @flg bit=0
  Set @Pattern1 = '%[^ ][A-Z]%'

   While PatIndex(@Pattern1 collate Latin1_General_Bin, @Temp) > 0
BEGIN
        Set @Temp = Stuff(@Temp, PatIndex(@Pattern1 collate Latin1_General_Bin, @Temp) + 1, 0, ' ')

       set @flg=1
END
    if(@flg=0)
    if   PatIndex('%s%' , substring(@Temp,len(@temp),1)) >0
         Set @Temp = substring(@Temp,0,len(@temp))

select @Temp

--'FlightName' return Flight Name
--Stations return Station
-- foo return foo
--FlightWeeks return Flight Weeks

Is there a better way to do multiple replace on a string?

3 Answers3