CODEWITHSUNDEEP
mysql
Ankita Kashyap
Ankita Kashyap

Reputation: 517

mysql pick particular string only

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

Upvotes: 0

Views: 27

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

You can use substring_index twice like this:

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

Demo

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

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

Upvotes: 1

Related Questions