looking for images
-
Hi everyone.
I must filter from a table all the rows that contains images and get all the file names of this images. Take a look to the example bellow :
. funciona!</p> <p> </p> <p><IMG border=0 src="ficheiros/inicia1.jpg" >></p> <p>
Looking for the images is an easy task using like operator (like '%img%'), returning the part of the string that contains the image file is more dificult...
Does anyone knows how to do it ?
-
SUBSTRING(col1,CHARINDEX('src="',col1,CHARINDEX('<IMG',col1))+5,
CHARINDEX('"',col1,CHARINDEX('src="',col1,CHARINDEX('<IMG',col1))+5)-
CHARINDEX('src="',col1,CHARINDEX('<IMG',col1))-5)
providing the filename is always ecapsulated by double-quotes
Far away is close at hand in the images of elsewhere.
Anon. -
Hi David,
Thanks for the Tip.
I'm running the following query:
select
SUBSTRING(texto,CHARINDEX('src="',texto,CHARINDEX('<IMG',texto))+5,
CHARINDEX('"',texto,CHARINDEX('src="',texto,CHARINDEX('<IMG',texto))+5)-
CHARINDEX('src="',texto,CHARINDEX('<IMG',texto))-5)
from artigos
where texto like '%img%'
She returning 236 rows , but ends with an error:
(236 row(s) affected)
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
I don't if its a warning or if there is further rows that are not processed due to this error ...
-
This is because one of the CHARINDEX is returning 0 (string not found) and being subtracted from resulting in a negative number for length to a substring, hence the error.
What is the datatype of 'texto'?
charindex will only search the first 8000 bytes of text columns, if it is TEXT then you will have to use PATINDEX instead.
Far away is close at hand in the images of elsewhere.
Anon. -
Bit less straightforward with PATINDEX() - no startpos parameter. Here's a pretty rough bit of example code, which might form the basis of a solution. Don't know what performance would be like on larger recordsets, though.
- use
adventureworks
- declare
@outertagopen varchar(20), @tagclose varchar(20), @innertagopen varchar(20), @innertagclose varchar(20), @outertagoplen int, @closelen int, @innertagoplen int, @innertagcllen int
- select @outertagopen = 'I ', @tagclose = '.', @innertagopen = ' a ', @innertagclose = '.'
- ,
@outertagoplen = datalength(@outertagopen)
- ,@closelen = datalength(@tagclose)
- ,@innertagoplen = datalength(@innertagopen)
- ,@innertagcllen = datalength(@innertagclose)
- select
- substring
(V2.string,1,patindex('%'+@innertagclose+'%', V2.string)-1) string
- from
- (
- select
- substring
(V.string,patindex('%'+@innertagopen+'%', V.string) + @innertagoplen ,8000) string
- from
- (
- select
substring(p.Comments, patindex('%' + @outertagopen + '%',p.Comments)+@outertagoplen,8000) string
- from
Production.ProductReview p
- where
patindex('%'+@innertagopen+'%',substring(p.Comments,patindex('%' + @outertagopen + '%',p.Comments)+@outertagoplen,8000)) > 0
- and
patindex('%'+@innertagopen+'%',substring(p.Comments,patindex('%' + @outertagopen + '%',p.Comments)+@outertagoplen,8000))
- < patindex('%'+@tagclose+'%',substring(p.Comments,patindex('%' + @outertagopen + '%',p.Comments)+@outertagoplen,8000)+@tagclose)
- and
patindex('%'+@innertagclose+'%',substring(p.Comments,patindex('%'+@innertagopen+'%',substring(p.Comments,patindex('%' + @outertagopen + '%',p.Comments)+@outertagoplen,8000))+@innertagoplen,8000)) > 0
- )
V
- where
patindex('%'+@innertagopen+'%',V.string) < patindex('%'+@tagclose+'%',V.string+@tagclose)
- ) V2
- where
patindex('%'+@innertagclose+'%',V2.string) <= patindex('%'+@tagclose+'%',V2.string+@tagclose)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Not much
SUBSTRING(col1,
PATINDEX('%<IMG%',col1) + PATINDEX('%src="%',SUBSTRING(col1,PATINDEX('%<IMG%',col1),255))+4,
PATINDEX('%"%',
SUBSTRING(col1,PATINDEX('%<IMG%',col1) +
PATINDEX('%src="%',SUBSTRING(col1,PATINDEX('%<IMG%',col1),255))+4,255)) - 1)
Far away is close at hand in the images of elsewhere.
Anon. -
It crossed my mind that if you want to store this kind of data, the XML datatype might be of use. I have a feeling that it might be possible to store (well-formed) HTML as XML, i.e., I guess, there is a well-formed XML schema againt which well-formed HTML can be validated. Or did I dream it?
David: Yes, in our context 'a bit' entails 'not much' (and is equivalent to 'somewhat, but not much').
The code I posted differs from your 'code snatch' mainly in having a maximally selective WHERE clause, putting parameters and some derived values in variables, and by using a couple of trivial superqueries to make the code more readable. The lattermost is done in the (admittedly unsafe from my epistemic situation) assumption that the optimiser will treat that style of defining the resultset as no more 'difficult' than the nested functions in your fully expanded version.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
...a well-formed XML schema againt which well-formed HTML can be validated. There is a XML Schema for XHTML
David: Yes, in our context 'a bit' entails 'not much' (and is equivalent to 'somewhat, but not much'). Like it
The code I posted differs from your 'code snatch' mainly... My post was bit tongue in cheek I suppose
Yours will probably run better
I don't know 
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply