Replace url with <a href="url">url</a> from a string using SQL

Question

Replace url with <a href="url">url</a> from a string using SQL

StillAlive

SSC Veteran

Points: 270
More actions
April 12, 2015 at 1:35 pm

#299752

Hey People!
I have a column in the table where i store a paragraph entered from UI. This paragraph contains one or two URLs, which may appear in starting or middle or in the end of paragraph. I need to replace all saved paragraphs (logically update that column) that contain URL(s). I only know every URL starts from 'http...', but don't know what it ends (it might be - '.aspx' OR '.php' OR '/' etc).
Could someone please help me out with a detailed solution to do so?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Not much for details. BOL (Books On Line... hiding under the F1 key) is your friend. Check out this article on CHARINDEX. It should get you started. You'd basically grab all the text between the start of "http" and the next space, and that would give you your URL. Maybe look up Jeff Moden's string splitting articles (using a Numbers or Tally table).

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

Here is a little snip to play with

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TBL_PARAGRAPH') IS NOT NULL DROP TABLE dbo.TBL_PARAGRAPH;

CREATE TABLE dbo.TBL_PARAGRAPH

(

PG_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_PARAGRAPH_PG_ID PRIMARY KEY CLUSTERED

,PG_TEXT VARCHAR(8000) NOT NULL

);

INSERT INTO dbo.TBL_PARAGRAPH (PG_TEXT)

VALUES

('<a href="URL01">First link</a> and some text and the <a href="URL02">second link</a>')

,('<a href="URL03">Only link</a> and some text')

,('This one is in <a href="URL04">the middle</a> of the text')

,('Some text and the <a href="URL05">only link</a>');

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,PARSED_SET AS

(

SELECT

TP.PG_ID

,TP.PG_TEXT

,NM.N

,ROW_NUMBER() OVER

(

PARTITION BY TP.PG_ID

ORDER BY NM.N

) + (ROW_NUMBER() OVER

(

PARTITION BY TP.PG_ID

ORDER BY NM.N

) % 2) AS GRP_NO

,ASCII(SUBSTRING(TP.PG_TEXT,NM.N,1)) AS CHR_CODE

,SUBSTRING(TP.PG_TEXT,NM.N,1) AS CHR_VAL

FROM dbo.TBL_PARAGRAPH TP

CROSS APPLY (SELECT TOP (LEN(TP.PG_TEXT)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4) AS NM(N)

WHERE ASCII(SUBSTRING(TP.PG_TEXT,NM.N,1)) IN (34,60,62)

)

,URL_PARSE AS

(

SELECT

MIN(PS.PG_ID) AS PG_ID

,PS.PG_TEXT

,PS.GRP_NO

,MIN(PS.N) AS MIN_N

,MAX(PS.N) AS MAX_N

,SUM(PS.CHR_CODE) AS SUM_CHAR_CODE

FROM PARSED_SET PS

GROUP BY PS.GRP_NO

,PS.PG_TEXT

HAVING MIN(PS.CHR_VAL) = '"'

)

SELECT

UP.PG_ID

,SUBSTRING(UP.PG_TEXT,1 + MAX(CASE WHEN UP.SUM_CHAR_CODE = 94 THEN UP.MAX_N END),(MAX(CASE WHEN UP.SUM_CHAR_CODE = 96 THEN UP.MIN_N END)- MAX(CASE WHEN UP.SUM_CHAR_CODE = 94 THEN UP.MAX_N END)) - 1)

,UP.PG_TEXT

FROM URL_PARSE UP

GROUP BY UP.PG_ID

,UP.PG_TEXT

,(UP.GRP_NO - CASE WHEN UP.SUM_CHAR_CODE = 96 THEN 2 ELSE 0 END)

;

Results

PG_ID URL PG_TEXT

----------- -------- --------------------------------------------------------------------------------------

1 URL01 <a href="URL01">First link</a> and some text and the <a href="URL02">second link</a>

1 URL02 <a href="URL01">First link</a> and some text and the <a href="URL02">second link</a>

2 URL03 <a href="URL03">Only link</a> and some text

3 URL04 This one is in <a href="URL04">the middle</a> of the text

4 URL05 Some text and the <a href="URL05">only link</a>

StillAlive SSC Veteran Points: 270 More actions · Answer 3

Thanks People! I was running out of time, so I had to drop the idea to do it using SQL.

pandey.prash Newbie Points: 5 More actions · Answer 4

Hello Experts,

the post mentioned above is now my problem to deal with.

I have data in sql table which contains web address and may have one or many web address any where mentoned.

for example:

Control of Vibration at Work Regulations: http://www.hse.gov.uk/vibration/hav/regulations.htm http://www.legislation.gov.uk/uksi/2005/1093/contents/made

now I have been asked to add anchored tag before and after every web addrress to make the link clickable.

that means web address before

http://www.hse.gov.uk/vibration/hav/regulations.htm

should be look like

a href="http://www.hse.gov.uk/vibration/hav/regulations.htm/"><u>http://www.hse.gov.uk/vibration/hav/regulations.htm/</u>

and the web address may appear in beginning or after any sentence.

Kindly assist.

Thanks

Prashant Pandey

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

StillAlive wrote:

Thanks People! I was running out of time, so I had to drop the idea to do it using SQL.

What did you use instead?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)