extract filename and update DB

[murphyhg]

I have inherited a db which has this for a file name.
<img src="images/thumbs/thumbwestplaza.jpg" alt"West Plaza">

How can I extract thumbwestplaza.jpg and update the database with that. I would do it by hand but there are too many records.

Thanks for your help always!

 

[SQLBill]

I don't have a script ready, nor time to put one together, but here is what I would do.

Since the part you are looking for is between a slant (/) and quotes ("), I would find the location of the " before the alt. Then from there find the first / before that. This might require using REVERSE to reverse the string.

If I have time, I'll work on a script...

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[murphyhg]

SQLBILL, to be honest that is totally over my head. If you can help it would be most appreciated.

 

[markros]

First you split your string based on " and then based on / to get the final file name.

Here are two blogs that solve this problem

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/passing-multiple-ranges-to-stored-proced

http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/tokenizing-filenames-in-sql-server

PluralSight Learning Library

 

[simian336]

Reverse it, find the period, find the first /. Then go from 3 minus the period to the / and reverse it again.

declare @test varchar(200)
set @test= '<img src="images/thumbs/thumbwestplaza.jpg" alt"West Plaza">'

Select CHARINDEX('.', reverse(@test)), CHARINDEX('/', reverse(@test)), reverse(@test)

select reverse(SUBSTRING(
reverse(@test),
CHARINDEX('.', reverse(@test))-3,
CHARINDEX('/', reverse(@test))-CHARINDEX('.', reverse(@test))+3
))

Simi