How to insert a URL link string into a nvarchar(1000)

Transact-SQL
1

Hi,

Let's say I have this link and want to insert it into a field. What is the right way to escape/replace those single quote double quote characters so the insert will work, and when it is retrieved, the original syntax is preserved?

So here is the string:
<a href="http://www.oursite.org/page1/pageX/download.php?fileinfo=VeryLong9rcy9iVldmFsbLnBkZg==&sectiondetailid=123456"" target="_blank"
onclick="window.open('http://www.anothersite.com/page/download.php?fileinfo=AnotherLongmlsZTIxN5wZGY=&sectiondetailid=98765','_newPage','toolbar=1,resizable=1');">Additonal InformationBy clicking yes, I approved XYZ

if object_ID('tempdb..#dummyURL') is not null drop table dummyURL;

create table #dummyURL (URL_LINK nvarchar(1000))

--this(=just added ' ') wouldn't work.
insert into #dummyURL (URL_LINK)
Select
'<a href="http://www.oursite.org/page1/pageX/download.php?fileinfo=VeryLong9rcy9iVldmFsbLnBkZg==&sectiondetailid=123456"" target="_blank"
onclick="window.open('http://www.anothersite.com/page/download.php?fileinfo=AnotherLongmlsZTIxN5wZGY=&sectiondetailid=98765','_newPage','toolbar=1,resizable=1');">Additonal InformationBy clicking yes, I approved XYZ.'

--but this next worked (after replaced " with " and ' with &#39:)

insert into #dummyURL (URL_LINK)
Select
'<a href="http://www.oursite.org/page1/pageX/download.php?fileinfo=VeryLong9rcy9iVldmFsbLnBkZg==&sectiondetailid=123456"" target="_blank"
onclick="window.open('http://www.anothersite.com/page/download.php?fileinfo=AnotherLongmlsZTIxN5wZGY=&sectiondetailid=98765','_newPage','toolbar=1,resizable=1');">Additonal Information
By clicking yes, I approved XYZ.'
drop table #dummyURL

--but since I don't have a way to validate the returned value, I have to ask.

Thanks!

2

Your URL should come in the form of a variable. That should go directly into the table [code]DECLARE @myvar VARCHAR(MAX) =
'onclick="window.open(''http://www.anothersite.com/page/download.php?fileinfo=AnotherLongmlsZTIxN5wZGY=&sectiondetailid=98765'',''_newPage'',''toolbar=1,resizable=1'');">Additonal Information
By clicking yes, I approved XYZ.'
;
if object_ID('tempdb..#dummyURL') is not null drop table dummyURL;
create table #dummyURL (URL_LINK nvarchar(1000));
INSERT INTO #dummyURL (URL_LINK)
SELECT @myvar;

select URL_LINK from #dummyURL;

[/code]

1 Like
3

that is a great idea! thanks!

However, same challenge. Sorry my pasted string sample missed out the beginning part, it should start with a A link tag as href="http://www.siteABC/:.. I have to remove the < at the beginning to make it show here.

a href="http://www.oursite.org/page1/pageX/download.php?fileinfo=VeryLong9rcy9iVldmFsbLnBkZg==&sectiondetailid=123456"" target="_blank" "
onclick="window.open('http://www.anothersite.com/page/download.php?fileinfo=AnotherLongmlsZTIxN5wZGY=&sectiondetailid=98765','_newPage','toolbar=1,resizable=1');">Additonal InformationBy clicking yes, I approved XYZ

so to use the var, it complains the incorrect syntax near '/'.