Convert plain text URL in true HTML links

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert plain text URL in true HTML links

Author

Topic

MatthieuQ
Starting Member

9 Posts

Posted - 2003-10-21 : 12:00:10

Hello,

I've got tables with text type values.
The records contain sentences with text-only URLs anywhere.

For example :
blabla nla bla bla http://www.yahoo.com bla bla (...) bla http://www.altavista.com.com blablabla

I would like to modify the value of the field to obtain the following result :

blabla nla bla bla <a href=http://www.yahoo.com
target=_blank>http://www.yahoo.com.com</a> bla bla (...) bla <a href=http://www.altavista.com target=_blank>http://www.altavista.com</a> blablabla

I precise that the URL are all differents, there can be 1 or more URL typed.

Any idea ?

Thanks by advance for any help given.

Matthieu

PS : We can imagine this table :

CREATE TABLE tbl (col VARCHAR(500) NOT NULL PRIMARY KEY);
GO

INSERT tbl SELECT 'blabla nla bla bla http://www.microsoft.com bla ';
INSERT tbl SELECT 'http://www.yahoo.com bla bla';
INSERT tbl SELECT 'bla bla bla bla http://www.google.com bla bla bla';

I post here a script that functions in SELECT mode. I got it in another sql forum. The goal is to realise an UPDATE Query with the result of the following query (and I'm not good enough to find it ) :

SELECT REPLACE(col, c1, '<a href=' + c1 + ' target=_blank>' + c1 + '</a>')
FROM
(SELECT col, SUBSTRING(col, CHARINDEX('http://', col), ABS(CHARINDEX(SPACE(1), col, CHARINDEX('http://', Liens)) - CHARINDEX('http://', col))) FROM tbl WHERE col LIKE '%http://%') D (col, c1)

Any idea ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-21 : 13:21:24

That one was tricky.



SET NOCOUNT ON

CREATE TABLE tbl (col VARCHAR(500) NOT NULL PRIMARY KEY);
GO

INSERT tbl SELECT 'blabla nla bla bla http://www.microsoft.com bla ';
INSERT tbl SELECT 'http://www.yahoo.com bla bla';
INSERT tbl SELECT 'bla bla bla bla http://www.google.com bla bla bla';

UPDATE tbl
SET tbl.col = REPLACE(col, SUBSTRING(col, CHARINDEX('http://', col), ABS(CHARINDEX(SPACE(1), col, CHARINDEX('http://', col)) - CHARINDEX('http://', col))), '<a href=' + SUBSTRING(col, CHARINDEX('http://', col), ABS(CHARINDEX(SPACE(1), col, CHARINDEX('http://', col)) - CHARINDEX('http://', col))) + ' target=_blank>' + SUBSTRING(col, CHARINDEX('http://', col), ABS(CHARINDEX(SPACE(1), col, CHARINDEX('http://', col)) - CHARINDEX('http://', col))) + '</a>') 
FROM
(SELECT SUBSTRING(col, CHARINDEX('http://', col), ABS(CHARINDEX(SPACE(1), col, CHARINDEX('http://', col)) - CHARINDEX('http://', col))) c1 FROM tbl WHERE col LIKE '%http://%') D

SELECT *
FROM tbl

DROP TABLE tbl

Tara

MatthieuQ
Starting Member

9 Posts

Posted - 2003-10-22 : 04:30:22

Thank you very much Tara.
I've got one more question. I hope you can help me.
This query works with varchar fields. Is it possible to make it work with text (memo) fields ?
Thanks by advance

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-22 : 06:20:02

Nope

TSQL isn't the best place for doing stuff like this, it will always be a bit of a kludge.
You are better off doing it in another tier, like in ASP.NET with some regexps.

Damian

MatthieuQ
Starting Member

9 Posts

Posted - 2003-10-22 : 06:27:31

I'm looking at this time to solutions with regular expressions. But I'm just a newbie at this time !
Have you got some links I could check ?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-22 : 06:29:19

What are you developing your front end in ?

Damian

MatthieuQ
Starting Member

9 Posts

Posted - 2003-10-22 : 06:36:01

Classic Active Server Pages application.
In fact, I've got a routine that can transforme http://www.google.com in <a href=http://www.google.com>http://www.google.com</a>. This routine is called at the loading of theASP page.
The probleme is :
If there is the "<a href=http://www.google.com>http://www.google.com</a>" in the field, my routine doesn't work anymore. I would like to be able to put "simple" hand writed url (without HTML tags) and "true" html links (with HTML tags).

I don't know if it's clear...

Here is the routine (I've found some code on the web and I modified it) :

Dim LaChaine
Function LaunchConversionToUrls(inputStr)

LaChaine = inputStr
CALL convertIntoURL (LaChaine, "http://")
CALL convertIntoURL (LaChaine, "ftp://")
LaunchConversionToUrls = LaCHaine
END Function

' Convert URLs into Hyperlinks - Source Code

' Function to convert all URL strings into Hyperlinks in a given string.

SUB convertIntoURL(inputStr, TypeDeRecherche)
Dim startPos, outputStr, posURL, posURLEnd, urlStr, linkStr

startPos = 1
outputStr = ""
WHILE startPos < LEN(inputStr)
posURL = InStr(startPos, LCASE(inputStr), TypeDeRecherche)

IF posURL > 0 THEN
IF posURL = 1 THEN
outputStr = outputStr & _
MID(inputStr, startPos, posURL - startPos)
ELSE
outputStr = outputStr & _
MID(inputStr, startPos, posURL -1 - startPos + 1)
END IF
outputStr = outputStr & " <a href="""
posURLEnd = InStr(posURL, inputStr, " ")

IF posURLEnd < 1 THEN
posURLEnd = LEN(inputStr) + 1
END IF

urlStr = MID(inputStr, posURL, posURLEnd - posURL)

SELECT CASE TypeDeRecherche
CASE "http://"
linkStr = urlStr
CASE "ftp://"
linkStr = urlStr
END SELECT

outputStr = outputStr & linkStr & """ target=_blank>"
outputStr = outputStr & urlStr & "</a>"
ELSE
outputStr = outputStr & MID(inputStr, startPos)
posURLEnd = LEN(inputStr)
END IF

startPos = posURLEnd
WEND
LaChaine = outputStr
END SUB

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-22 : 06:40:28

Have a read of this http://www.4guysfromrolla.com/webtech/061399-3.shtml
It works pretty well, I've used this (with some tweaks) on a bunch of projects over the years.

Damian

MatthieuQ
Starting Member

9 Posts

Posted - 2003-10-22 : 07:15:18

Well, I've changed the ASP function of the 4guysfrom rolla link to :

<%
Function to_html(s_string)
to_html = Replace(s_string, """", """)
' to_html = Replace(to_html, "<", "<") 'comments
' to_html = Replace(to_html, ">", ">") 'comments
to_html = Replace(to_html, vbcrlf, "<br>")
to_html = Replace(to_html, "/<", "<")
to_html = Replace(to_html, "/>", ">")
to_html = edit_hrefs(to_html)
End Function
%>

It seems to work perfectly for HTML links. Less for email. Do you think if the javascript function may be converted to ASP vbscript function ?

I'll try to midify the function to insert "target=_blank" in each link...

Thank you for the link...

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-10-22 : 07:17:26

~~I don't think nither the nor tara have considered strings with multipl urls.~~

EDIT: ok, that was real bad ...
I don't think neither the original poster nor tara have considered strings with multiple urls.

Jay White
{0}

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-22 : 07:23:03

English Jay, do you speak it ?

You could port that part of code to vbscript. When the article was written VBScript didn't do regular expressions. It does now, but it's a bit more painful, why change what is there ?

Damian

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-22 : 07:57:58

quote:

I don't think nither the nor

I don't think THAT's a "regular expression", either !

- Jeff

MatthieuQ
Starting Member

9 Posts

Posted - 2003-10-22 : 07:59:52

Yeah, the regular expression is in the Javascript function. More details here http://www.4guysfromrolla.com/webtech/061399-3.shtml :)

MatthieuQ
Starting Member

9 Posts

Posted - 2003-10-22 : 08:04:44

I would like to have only text code on the result page and not Javascript functions.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-22 : 08:28:13

The javascript in that example is server side. It won't show up in your rendered HTML.

Damian

MatthieuQ
Starting Member

9 Posts

Posted - 2003-10-22 : 08:58:58

Hum, yes. You're right.
Sorry.

I'll try to modify the function to insert "target=_blank" in each link...

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-22 : 09:02:09

Here is a replacement for that function, it has target=_blank and a few minor bugfixes I have applied over the years.



function edit_hrefs(s_html){

    s_str = new String(s_html);

    s_str = s_str.replace(/\bhttp\:\/\/www(\.[\w+\.\:\/\_\?=&%]+)/gi, 
		"http\:\/\/¬¤¸$1");

    s_str = s_str.replace(/\b(http\:\/\/\w+\.[\w+\.\:\/\_\?=&%]+)/gi,
		"<a href=\"$1\" target=\"_blank\">$1<\/a>");
		
    s_str = s_str.replace(/\b(www\.[\w+\.\:\/\_\?=&%]+)/gi, 
		"<a href=\"http://$1\" target=\"_blank\">$1</a>");
		
    s_str = s_str.replace(/\bhttp\:\/\/¬¤¸(\.[\w+\.\:\/\_\?=&%]+)/gi,
		"<a href=\"http\:\/\/www$1\" target=\"_blank\">http\:\/\/www$1</a>");
		
    s_str = s_str.replace(/\b([\w\.]+@[\w+\.?]*)/gi, 
		"<a href=\"mailto\:$1\">$1</a>");
		
    
    return s_str;
}

Damian

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-22 : 09:07:28

*WARNING* I have not tested this.
Here I assume the following DDL for table t:
create table t (PK int primary key, TextCol text)

The code below is for making needful replacing in only
one row of the table t - where PK=5 (no problem to process
all rows in a loop):

declare @pointer binary(16), @i int, @j int, @s varchar(80)
set @i=-1

while @i<>0
begin
select @pointer=textptr(TextCol), @i=patindex('% http://%',TextCol)
from t where PK=5
if @i>0
begin
select @s=substring(TextCol,@i+1,80) from t where PK=5
set @s=substring(@s,1,charindex(' ',@s)-1)
set @j=len(@s)+1
set @s='<a href='+@s+' target=_blank>'+@s+'</a>'
set @i=@i-1
updatetext t.TextCol @pointer @i @j @s
end
end

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-22 : 09:19:54

LOL

Seems my previous post here is already needless...

Anyway it was interesting to give a try on that awesome UPDATETEXT.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-22 : 12:05:40

quote:
Originally posted by Page47

~~I don't think nither the nor tara have considered strings with multipl urls.~~

EDIT: ok, that was real bad ...
I don't think neither the original poster nor tara have considered strings with multiple urls.

Jay White
{0}

I just worked with the sample data. The sample data should represent what kind of data to expect, so that's what I go off. If it doesn't, then the thread starter needs to provide more relevant sample data.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-22 : 15:04:48

Works fine.
Those who interested can check it. Just bulk-insert into empty table t
only one row from a big text file with written into its text several urls.
The only limitation: each url must have 2 (left and right) trailing spaces.

if object_id('t')>0 drop table t
GO
create table t(TextCol text)
GO
bulk insert t from 'D:\my.txt'
with (rowterminator='@@@')
GO

declare @pointer binary(16), @i int, @j int, @s varchar(80)
set @i=-1

while @i<>0
begin
select top 1 @pointer=textptr(TextCol), @i=patindex('% http://%',TextCol)
from t
if @i>0
begin
select top 1 @s=substring(TextCol,@i+1,80) from t
set @s=substring(@s,1,charindex(' ',@s)-1)
set @j=len(@s)
set @s='<a href='+@s+' target=_blank>'+@s+'</a>'
updatetext t.TextCol @pointer @i @j @s
end
end

select substring(TextCol,1,255) from t

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources