CODEWITHSUNDEEP
sqlsql-server
AngryHacker
AngryHacker

Reputation: 61636

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

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?

Upvotes: 0

Views: 1201

Answers (3)

KumarHarsh
KumarHarsh

Reputation: 5094

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

Upvotes: 0

SelvaS
SelvaS

Reputation: 2125

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

Upvotes: 0

swe
swe

Reputation: 1455

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

Upvotes: 1

Related Questions