Find and Replace using SQL

I posted this in the Access forum too, but I think this may be a better
place...
=======================================

I have an access field that was a hyperlink, now converted to text. It is
messy. Here's an example of what it looks like after converted to text.

020411-1568-07-25-08-Depo
Docs#E-File%20Indices/1568/Depo.%20Docs%20from%20S.Haney%20file/020411-1568-07-25-08-Depo%20Docs.PDF#

I need to do the following and I think a SQL statement is what I need but
don't know enough to do it without help.

I need to find and delete anything that exists before /1568
I need to find and rename /1568 to 1568
I need to find and rename / to \
I need to find and rename %20 to a space
I need to find and rename 1568\ to t:\
I need to find and delete #

The end result should look like this

t:\1568\Depo. Docs from S.Haney file\020411-1568-07-25-08-Depo Docs.PDF

I then need to convert it back to a hyperlink.

Thank you for your help.

On Fri, 16 Jan 2009 16:10:01 -0800, HWhite wrote:

>I posted this in the Access forum too, but I think this may be a better
>place...

Hi HWhite,

That depends. If you need a solution in Access, the Access forum is a
better place. This is a SQL Server group, so you'll get a SQL Server
solution here.

(snip)
>I need to find and rename 1568\ to t:\
(snip)
>The end result should look like this
>
>t:\1568\Depo. Docs from S.Haney file\020411-1568-07-25-08-Depo Docs.PDF

This doesn't match the above part of the spec. I'll give you two
solutions, one that matches the spec and one that matches the sample
output.

CREATE TABLE Test (TheText varchar(200));
go
INSERT INTO Test(TheText)
VALUES ('020411-1568-07-25-08-Depo
Docs#E-File%20Indices/1568/Depo.%20Docs%20from%20S.Haney%20file/020411-1568-07-25-08-Depo%20Docs.PDF#');
go
UPDATE Test
SET TheText = REPLACE(REPLACE(REPLACE(STUFF(TheText, 1,
PATINDEX('%1568/%', TheText) + 4, 't:\1568\'), '/', '\'), '%20', ' '),
'#', '');
--SET TheText = REPLACE(REPLACE(REPLACE(STUFF(TheText, 1,
PATINDEX('%1568/%', TheText) + 4, 't:\'), '/', '\'), '%20', ' '), '#',
'');
SELECT * FROM Test;
go
DROP TABLE Test;
go

>I then need to convert it back to a hyperlink.

That is a client-side function. SQL Server doesn't know of any
hyperlinks.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis