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.
| Author |
Topic |
|
ConradK
Posting Yak Master
140 Posts |
 Posted - 2010-04-30 : 13:36:20
|
| So I have a column where each row is a mess of characters, and there is a url within that mess of characters I'de like to pull. The data looks something like this.Dataadfadfdahttp://image.com/image1.jpgadfadsfdafaahttp://image.com/image2.jpgasdfadaadggddahttp://image.com/image3.jpgdfgdgdfaaasdfawwhttp://image.com/image4.jpgssdfdahttp://image.com/image5.jpg3rtshaf4so the image itself is obviously delimited. I'de like to pull information starting with 'http' and ending with 'jpg'. For the life of me I can't think of a way to do this at all.Any thoughts?Thanks in advance guys! |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2010-04-30 : 13:44:38
|
| declare @myval varchar(1000)set @myval='abcdefhttp://image.com/image5.jpg3rtshaf4'select charindex('http',@myval),charindex('jpg',@myval),substring(@myval,charindex('http',@myval),((charindex('jpg',@myval)+3)-charindex('http',@myval)))Mike"oh, that monkey is going to pay" |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2010-04-30 : 13:45:30
|
| actually the cleaned up version would be this.declare @myval varchar(1000)set @myval='abcdefhttp://image.com/image5.jpg3rtshaf4'select substring(@myval,charindex('http',@myval),((charindex('jpg',@myval)+3)-charindex('http',@myval)))Mike"oh, that monkey is going to pay" |
|
|
|
ConradK
Posting Yak Master
140 Posts |
Posted - 2010-04-30 : 13:49:47
|
| cool! seems like it works! Now to figure out why.... thanks alot for your help! |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2010-04-30 : 14:09:01
|
| lol..that's the fun part. basically char index tells me where http and jpg are in the string. Then I want to get everything in between but add 3 characters because jpg is 3 characters long.Mike"oh, that monkey is going to pay" |
|
|
|
|
|
|
|
|
Grab a gem inside garbage