CODEWITHSUNDEEP
mysqlsqlselect
Miko Meltzer
Miko Meltzer

Reputation: 53

How to cut part of a string in MySQL?

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?

Upvotes: 5

Views: 20389

Answers (3)

Pradeep Kumar
Pradeep Kumar

Reputation: 4141

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');

Upvotes: 0

Denys Séguret
Denys Séguret

Reputation: 382464

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.

Upvotes: 8

Bjoern
Bjoern

Reputation: 16314

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.

Upvotes: 3

Related Questions