Mysql Question posted in
The official MySQL documentation can be found here.

How to select a substring from a column value in mysql 5.7?

the selected values of my query are like

gruppo-1_some_text
gruppo-12_some_other_text

But I’d like to have only

some_text
some_other_text

So I would need to substract 'gruppo-d+_' from the initially selected strings.

I don’t have REGEXP_REPLACE and the like on mysql 5.7.39, so I can’t figure out how to do it, if ever possible…

Tags:

2

Answers


  1. 0

    See example

    select txt 
  ,case when (txt regexp 'gruppo-[0-9]{1,}_')=1 then
    substring(substring(txt,length('gruppo-')+1,100)
     ,instr(substring(txt,length('gruppo-')+1,100),'_')+1,100) 
   end s
  ,case when (txt regexp 'gruppo-[0-9]{1,}_')=1 then
    substring(substring(txt,length('gruppo-')+1,100)
     ,1,instr(substring(txt,length('gruppo-')+1,100),'_')-1) 
   end n
from test

    create table test (id int,txt varchar(100));
insert into test values
 (1,'gruppo-1_some_text')
,(2,'gruppo-12_some_other_text')
,(3,'gruppo-_some_next_text')
,(4,'gruppo-44s_ome_next_text')
,(5,'gruppo-555 _some_next_text')
,(6,'gruppo-1234567890_some_next_text')
    txt s n
    gruppo-1_some_text some_text 1
    gruppo-12_some_other_text some_other_text 12
    gruppo-_some_next_text null null
    gruppo-44s_ome_next_text null null
    gruppo-555 _some_next_text null null
    gruppo-1234567890_some_next_text some_next_text 1234567890
    Login or Signup to reply.
  2. 0 
    SELECT id, txt, SUBSTRING(txt FROM 1 + LOCATE('_', txt)), txt REGEXP '^gruppo-\d+_.*'
FROM test
    id txt SUBSTRING(txt FROM 1 + LOCATE(‘_’, txt)) txt REGEXP ‘^gruppo-d+_.*’
    1 gruppo-1_text_1 text_1 1
    2 gruppo-12_text_12 text_12 1
    3 gruppo-_text_none text_none 0
    4 gruppo-44s_text_not_number text_not_number 0
    5 gruppo-555 _text_not_number_too text_not_number_too 0
    6 gruppo-1234567890_text_long text_long 1
    6 gruppo-0000000000_text_long_zerofill text_long_zerofill 1 
    SELECT id, txt, SUBSTRING(txt FROM 1 + LOCATE('_', txt))
FROM test
WHERE txt REGEXP '^gruppo-\d+_.*'
    id txt SUBSTRING(txt FROM 1 + LOCATE(‘_’, txt))
    1 gruppo-1_text_1 text_1
    2 gruppo-12_text_12 text_12
    6 gruppo-1234567890_text_long text_long
    6 gruppo-0000000000_text_long_zerofill text_long_zerofill

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search