mysql pick particular string only

Question

I have a column in database and having value like this

course_repeatfkfjkjfjkfer_10_topics_0_presentation_link
course_repeatfkfjfkfkfklfflkflkfs_1_presentation_link
course_repeatfkfjfkfkfklfflkflkfs_2_presentation_link
coursek_epeatfkfjfkfkfklfflkflkfs_10_presentation_link
course_hdhdhhdhdjhdrepeatfkfjfkfkfklfflkflkfs_21_presentation_link

and so on.

I need to pick 0,1,2,10,21, number before _presentation_link , But i need this in mysql as well

i used substr in mysql, but that is not working. Any idea?

Thanks

Tim Biegeleisen · Accepted Answer · 2017-03-03 04:40:37Z

1

One option would be to use a combination of SUBSTRING_INDEX() and REPLACE():

SELECT SUBSTRING_INDEX(REPLACE(col, '_presentation_link', ''), '_', -1)
FROM yourTable

Taking course_repeatfkfjkjfjkfer_10_topics_0_presentation_link as an example, after the replacement, this would become:

course_repeatfkfjkjfjkfer_10_topics_0

The call to SUBSTRING_INDEX() then grabs everything appearing after the final underscore, which is the number you want to capture.

Demo here:

SQLFiddle

answered Mar 3, 2017 at 4:40

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gurwinder Singh · Accepted Answer · 2017-03-03 04:41:28Z

0

You can use substring_index twice like this:

select substring_index(substring_index(col, '_', -3), '_', 1)
from t

Demo

answered Mar 3, 2017 at 4:41

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Collectives™ on Stack Overflow

mysql pick particular string only

2 Answers 2

SQLFiddle

Comments

Demo

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related