How do I pull a substring of various sides?

Question

If I want to update a column by pulling out only a part of a substring of another column.

What I want to do is pull the name of the jpg from the file and for example i want imageName to be equal to great-family.jpg" a varchar string. But the image names are all different.

update tblPetTips
set imageName = "great-family.jpg"
where articleText = "<img src="/images/imgs/great-family.jpg" alt="A Great Family Dog">"

In this case I would like to say

update tblPetTips
set imageName = "yellow-smile.jpg"
where articleText = "<img src="/images/imgs/yellow-smile.jpg" alt="A Yellow Smiley Face">"

How do I (without hardcoding) update imageName fromthe articleText column. All the directories are the same - all the images live in images/imgs.

Why not make this valid SQL first? The syntax highlighting shows you what's wrong there. And storing HTML? Yikes. — Lightness Races in Orbit, Sep 20 '14 at 19:23
Well I have to work with what is there and I can't change that -- i just need to pull the sring out — YelizavetaYR, Sep 20 '14 at 19:26
You cannot work with SQL statements that result in a syntax error. — Lightness Races in Orbit, Sep 20 '14 at 19:28
the articleText is the same across the board just different file names — YelizavetaYR, Sep 20 '14 at 20:54
These answers suck. Use a regular expression. Sorry, I do not do those for reputation points. Although, Lightness has some good points that would get rid of your problem altogether. — user17130, Sep 20 '14 at 21:25
@user17130 No backreferences with MySQL REGEXP(), so other string operations will do for this one-time-job as I see this question. Beginning with `src="` could make it a bit more robust though, if there should be some variation with the order of attributes. And if you think those are bad answers then you can downvote them, but please be polite. — VMai, Sep 20 '14 at 21:48
possible duplicate of [Parse file name and path from full path](http://stackoverflow.com/questions/19505875/parse-file-name-and-path-from-full-path) — Bulat, Sep 20 '14 at 22:05
I'm not sure what that means - any suggestions as to how to make the update statement work. — YelizavetaYR, Sep 20 '14 at 23:02
Maybe you can use [this monster](http://sqlfiddle.com/#!3/0f3c4/1). I've got to say that I'm used to T-SQL but not to such string operations. Maybe there's a simpler way, but for a one-time-job ... — VMai, Sep 21 '14 at 01:00

score 0 · Answer 1 · answered Sep 20 '14 at 21:12

0

You can try to get values between the last / and " with SUBSTRING_INDEX function:

UPDATE tblPetTips
SET imageName = SUBSTRING_INDEX(SUBSTRING_INDEX(articleText, '/', -1), '"', 1);

It will only work if format of <img srs=... > html is consistent.

answered Sep 20 '14 at 21:12

Bulat

6,494
1
25
47

Error: Msg 195, Level 15, State 10, Line 2 'SUBSTRING_INDEX' is not a recognized built-in function name. – YelizavetaYR Sep 20 '14 at 21:49
that is strange - http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index – Bulat Sep 20 '14 at 21:52
Apparently he doesn't know the difference between MySQL and SQL-Server, because he's actually using the latter. – Barmar Sep 20 '14 at 21:53
http://stackoverflow.com/questions/19505875/parse-file-name-and-path-from-full-path – Bulat Sep 20 '14 at 22:05
i'm helping out a friend - this is what i told there is - and i'm trying to help resolve some issues best i can. what is a good way to fix it. – YelizavetaYR Sep 20 '14 at 22:20
Any ideas how to fix it via TSQL? – YelizavetaYR Sep 20 '14 at 22:55
you can do it with combination of SUBSTRING and CHARINDEX like it is done here - http://stackoverflow.com/questions/19505875/parse-file-name-and-path-from-full-path – Bulat Sep 21 '14 at 08:55

score 0 · Accepted Answer · answered Sep 21 '14 at 03:20

if the source of your images is always /images/imgs/, you can use patindex to find the position of '/images/imgs/' and '" alt', then extract the text between them. check if this works:

substring(articletext, 
          patindex('/images/imgs/', articletext) + length('/images/imgs/'), 
          patindex('" alt') - (patindex('/images/imgs/', articletext) + length('/images/imgs/')))

if your images can have any url, then it would be feasible with regular expressions, but I don't think sqlserver provides regex directly. in that case you could write a function to extract the filename part of a url and call it in the update.

How do I pull a substring of various sides?

2 Answers2