coverting hyperlink # # - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

coverting hyperlink # #

Author

Topic

iankerry
Starting Member

7 Posts

Posted - 2009-03-06 : 09:01:58

hi

i recently upgrade to sql and one of my hyperlink fileds was converted to text making all my emails turn into an email address plus lots of info between 2 #'s e.g. email@email.co.uk#htt://email@email.co.uk#

all my records have this in - is there an update query way of selecting and deleteing any text between the two #'s?

many thanks

ian

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-06 : 09:10:03

Are there only 2 #s or could there be more?

iankerry
Starting Member

7 Posts

Posted - 2009-03-06 : 09:35:49

looks like without fail there are only two #'s, everything after (and including the first # could be deleted.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-06 : 09:37:59

Ok...Can you try this..

If you need both the #s
UPDATE urtable
SET @email = STUFF(@email,CHARINDEX('#',@email)+1,LEN(@email) -CHARINDEX('#',@email)-1,'')

If you dont need both the #s
UPDATE urtable
SET @email = STUFF(@email,CHARINDEX('#',@email),LEN(@email)+1 -CHARINDEX('#',@email),'')

iankerry
Starting Member

7 Posts

Posted - 2009-03-06 : 10:46:28

thanks VJ, just to clarify, @email = my email field name? and STUFF is what?

i just checked the syntax and it came up agument datatype ntext is invalid for arguement 1 of len function.

ooo errr.

iankerry
Starting Member

7 Posts

Posted - 2009-03-06 : 11:09:40

ok, done some digging which i should have done anyway before responding. sorry.

stuff is an sql command for deleting text. and its return value is the ntext which is coming up in my error. will do some more digging to see what it means!

ian

iankerry
Starting Member

7 Posts

Posted - 2009-03-06 : 11:28:31

CHARINDEX might be the problem? it doesn't work with ntext fields? Otherwise I can't find out what the problem is!

ian

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-06 : 12:06:32

which version of SQL Server are you using?

String operations cannot be performed on ntext anymore...convert your text,ntext fields to VARCHAR(MAX).

iankerry
Starting Member

7 Posts

Posted - 2009-03-06 : 12:11:11

ok thanks, will try then report back!