get number between two string by using substring

Question

I m trying to get number in between "sims_7009_alaira", i want 7009.

SELECT sno,dbase, SUBSTRING_INDEX(dbase, 'sims_', -1)temp
FROM school

How should i do that in SQL

Possible duplicate of [How to get a substring between two strings in PHP?](https://stackoverflow.com/questions/5696412/how-to-get-a-substring-between-two-strings-in-php) — , Feb 16 '18 at 11:27

score 2 · Accepted Answer · answered Feb 16 '18 at 11:32

2

Give this a try:

select substring_index(SUBSTRING_INDEX(dbase, '_', 2),'_',-1) from school;

Check this here: SQL Fiddle

answered Feb 16 '18 at 11:32

Yusuf Hassan

score 0 · Answer 2 · answered Feb 16 '18 at 11:31

0

Just use substring_index() two times:

SELECT sno, dbase, substring_index(substring_index(dbase, 'sims_', -1), '_alaira', 1) as number FROM school

answered Feb 16 '18 at 11:31

phpforcoders

score 0 · Answer 3 · answered Feb 16 '18 at 11:31

0

Do this instead:

SELECT sno,dbase, SUBSTRING_INDEX(SUBSTRING_INDEX(dbase, "_", 2),'_',-1) temp
FROM school;

For more insight see this.

answered Feb 16 '18 at 11:31

cdaiga

3 Answers3