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
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;
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 
Looks like i made a booo booo
Did not understand
I am looking for another job
I just lost this job
One Idea
cut the string into 3 parts
before /[
and after ]
replace the before and after
concat the parts again