T-sql 2012 update part of a string value with another string value

Transact-SQL
1

In a sql server 2012 database. I want to replace part of a value in the column called 'text' that is referring to one url link and replace the value with a new url link. This change needs to happen since the url (link) has changed in production recently.
The value that needs to be replaced is in the column listed below called 'stringtorepalce' with the new url link of 'https://dd1.org/app/letter.aspx'.
**Note the 'stringtoreplace' column had to be determined since the link in production has changed several times in the past 10 years.
SELECT text,
,PATINDEX('%https://%',text) as startlocation
,PATINDEX('%/letter.aspx%',text) as endlocation
,substring(text,PATINDEX('%https://%',text),PATINDEX('%/letter.aspx%',text) - PATINDEX('%https://%',text)) as stringtoreplace
FROM [dd1].[dbo].[Contact]
where text like '%spr.org%'
and text like '%/letter.aspx%'
and datetimestamp >= '2018-08-01 00:00:00.00'
order by datetimestamp desc
Basically the column called 'text' needs to be updated. Thus can you show me the sql on how to 'replace part of a value in the column called 'text' that is referring to one url link and replace the value with a new url link?

2

try this: If I understood you correctly?

Update [dd1].[dbo].[Contact]
set text = 'https://dd1.org/app/letter.aspx'

Pasi

3

please provide sample data in the following form so we can answer your question. we do not have access to [dd1].[dbo].[Contact]

do this

create table #Contact(text varchar(150), datetimestamp  datetime)

insert into #contact
select 'http://google.com', '2018-08-01 00:00:00.00' union
select 'http://cnn.com', '2018-08-01 00:00:00.00' union
select 'http://news.com', '2018-08-01 00:00:00.00'
4

sounds dangerous especially if they ran that on a production server. :scream:

1 Like
5

Just to start.. :slight_smile:

6

It would be something like:

Update [dd1].[dbo].[Contact]
Set
	text = REPLACE(text,
		substring(text,PATINDEX('%https://%',text),PATINDEX('%/letter.aspx%',text) - PATINDEX('%https://%',text)),
		'https://dd1.org/app/letter.aspx')
where text like '%spr.org%'
and text like '%/letter.aspx%'
and datetimestamp >= '2018-08-01 00:00:00.00'

But you'd definitely want to check the exact effects of doing so given what your data currently looks like. Depending on what the text contains, your code for finding the start and end points may be flawed - consider what happens if the text contains two URLs for example.

You will find the https bit from the start of the first URL, but the letter.aspx might come from the second and any replace would obliterate all the text in between. Unless you're very confident about the contents of the data (for example you know it can only contain one URL), I'd usually use a safer method such as identifying all the known old versions and updating each one individually, or a regular expression library that can better define the appropriate pattern matching than T-SQLs rather weak text handling.

7

I've run up against the need for this in Azure SQL Database, which uses .NET Core and does not have regular expression support. What do you recommend?

8

Unfortunately I couldn't say. Most of what I've done is on-prem and using a CLR module, but I don't think that's an option on Azure.

1 Like