using find/replace in sql?-CodePudding

I need to replace all "://" with "://" but only after "redirectUrl" string. if "redirectUrl" is not in the link - I should not do the replacement

link example where replacement should happens: http://tracking.company.com/click?redirectUrl=https://website.com

this code I am trying - does not work :(

Declare @Temp VarChar(100)
 Set   @Temp = 'http://tracking.company.com/click?redirectUrl=https://website.com'

 Declare @FindCharacter VarChar(10),
         @ReplaceCharacter VarChar(10)

Set @FindCharacter = '://'
Set @ReplaceCharacter = 'REPLACE'

 While PATINDEX('%redirectUrl%', @Temp) > 0
    Set @Temp = replace(@Temp, @FindCharacter,@ReplaceCharacter)
Select @Temp

CodePudding user response：

For example,

 Declare @Temp VarChar(100)
 Set   @Temp = 'http://tracking.company.com/click?redirectUrl=https://website.com'

Declare @FindCharacter VarChar(10),
         @ReplaceCharacter VarChar(10)

Set @FindCharacter = '://'
Set @ReplaceCharacter = 'REPLACE'

declare @p int = PATINDEX('%redirectUrl%', @Temp);
select case when @p=0 then @Temp 
  else substring(@Temp, 1, @p-1)   replace(substring(@Temp, @p, len(@Temp)), @FindCharacter, @ReplaceCharacter) end s;

CodePudding user response：

it works! thank you so much!

the reason why... I was told from IT that if we don't do these replacements the tracking won't work