How to cut part of a string in MySQL?

Question

In MySQL, I have a text column with "bla bla bla YYY=76767 bla bla bla".

I need to cut the number 76767.

How can I do it in SQL?

what would the output string `bla bla bla YYY= bla bla bla`?? — Vishwanath Dalvi, Jun 20 '12 at 07:23
Do you wish to update the actual data to take out the "76767" from the value? — Zane Bien, Jun 20 '12 at 07:36
Update instead: http://stackoverflow.com/questions/1876762/mysql-way-to-update-portion-of-a-string || regex replace: http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — Ciro Santilli OurBigBook.com, Sep 08 '15 at 07:45

Denys Séguret · Accepted Answer · 2012-06-20T07:44:11.197

8

You can use

select substring_index(substring(mycol, instr(mycol, "=")+1), " ", 1)

to get the first token after the =.

This returns 76767.

This works in two steps :

substring(mycol, instr(mycol, "=")+1)

returns the string starting after the =

and

substring_index( xxx , " ", 1)

get the first element of the virtual array you'd got from a split by " ", and so returns the first token of xxx.

edited Jun 20 '12 at 07:44

answered Jun 20 '12 at 07:21

Denys Séguret

More abstract/general: set `@frst` and `@scnd` as delimiters. The term you are looking for is between `@frst` and `@scnd`. In this case it is `SET @frst:='YYY='; SET @scnd:='';` You either want to update, so then `UPDATE tbl set my_col = SUBSTRING_INDEX(SUBSTRING(my_col , INSTR(my_col , @frst)+1), @scnd, 1) WHERE my_col LIKE '%@scnd%';` or you want to select, so `SELECT SUBSTRING_INDEX(SUBSTRING(my_col , INSTR(my_col , @frst)+1), @scnd, 1) from WHERE my_col LIKE '%@scnd%';` – kklepper Jan 07 '22 at 16:50

score 2 · Answer 2 · answered Jun 20 '12 at 07:26

2

The easiest approach to eliminate the number in your field would be just to REPLACE it.

SELECT REPLACE(yourcolumn, '76767', '')
FROM yourtable;

You can use a combination of SUBSTRING and INSTR, too.

answered Jun 20 '12 at 07:26

Bjoern

score 0 · Answer 3 · answered Mar 26 '15 at 07:57

0

If you wan't to UPDATE, use

UPDATE table_name SET column_name = REPLACE(column_name, '76767', '');

If you wan't to replace 76767 with bla bla bla in your db, use

UPDATE table_name SET column_name = REPLACE(column_name, '76767', 'bla bla bla');

answered Mar 26 '15 at 07:57

Pradeep Kumar

3 Answers3