SQLTeam.com | Weblogs | Forums

Change part of URLs

Hi, I'm having trouble using the REPLACE function to change some parts of a couple of hundred URLs. Each URL is the same except for the value in the brackets below.
path: /15d/[value]15d.pdf

I wish to change the two '15' to '14' for all the URLs but don't know how to do it due to the presence of the variable value.

Any guidance would be welcome
Alex

Welcome

Please test this out with test harness provided and see if this is what you want

declare @urls table(url varchar(1500))

insert into @urls
select distinct top 1000  concat('/15d/[',column_id,']15d.pdf')
  from sys.all_columns

select *, REPLACE (url, '15d','14d') as rplc
from @urls

--or
;with uno
as
(

select *, REPLACE (url, '/15d/','/14d/') as rplc
from @urls
),
dos as (
select *, REPLACE (rplc, '15d.pdf','14d.pdf') as rplc2
from uno
)
select url, rplc2 from dos

image

The problem with this is it might also replace the /[value] that might have 15d

Hi Yosiasz, is this an SQL query? I only have basic knowledge.

yes this is sql query. what database are you using? microsoft, mysql or postgres?

mysql

If you can do it in 2 update statements I would use the LEFT(url,5) and the RIGHT(url,8) to update the url.

DECLARE @URL VARCHAR(80);
SET @URL = '/15d/15d/15d/15d/15d/15d/15d.pdf';

SELECT 
	LEFT(@URL,4) AS [LEFT],
	REPLACE(LEFT(@URL,4),'15','14') AS LEFT_REPLACED,
	SUBSTRING(@URL,5,LEN(@URL)) AS OTHER_PART,
	CONCAT(REPLACE(LEFT(@URL,4),'15','14'),SUBSTRING(@URL,5,LEN(@URL))) AS ENDRESULT;

i am able to do it in T-SQL ... maybe some syntax issue with mySQL

this could also replace whatever is in value

/15d/[15 million dollars have been deposited into Harish's bank account]15d.pdf

you just lost one million with your REPLACE statement :laughing:

Looks like i made a booo booo

Did not understand

I am looking for another job
I just lost this job

:stuck_out_tongue_winking_eye::stuck_out_tongue_winking_eye::exploding_head::exploding_head:

One Idea

cut the string into 3 parts

before /[
and after ]

replace the before and after

concat the parts again