How to get a variable-size substring for each row in MySQL?

Question

I have a MySQL table with just a column with one text type with a lot of information in the same format starting from a row such as:

Field1 : 1234 Field2 : Something 

The column always starts with Field1 and Field2 , but the values ​​after each field are different for each record.

I need to get only the values ​​of what is after Field1 and Field2 (in this case 1234 and Something ), the value after Field1 is easy, because it is always 4 characters long, but the problem is after Field2 , because the size depends on each record, what i have so far:

 SELECT SELECT substring(substring_index(COLUMN,'Field1 : ',-1),1,4) as Field1_value FROM table 

I know that after Field2, the value has a line break, so I'll think about using the \ n character to delimit the desired value.

How to get this substring of variable size for each row?

PS Yes, the data is terribly structured, I can’t change it ...

Answer 1

Well, finally, I got it (thanks for your input to everyone), this is the final result of the request:

 select substring(subcolumn, 9,5) as Field1, substring(subcolumn, 24) as Field2 from ( select substring(COLUMN, 1, locate('\n',COLUMN,15)) as subcolumn from table ) as X 

Knowing the size of the lines "Field1:" and "Field2:", this isolates these 2 rows and their next values ​​from the rest of the column text, and then I use the substring (knowing that the value after field1 is always 4 characters), and 15 in the substring - so that I do not look at the break of the second line.

Answer 2

Set dynamic value as last parameter of substring ():

 select substring(your_column, length('FieldX : ')+1, length(your_column) - length('FieldX : ') + 1) as FieldX from your_table; 

This will work for any one bit X in the FieldX field.

Answer 3

If the format of the columns is always the same FieldX : then:

 SELECT SUBSTRING(COLUMN, LOCATE(':', COLUMN)+2) as FieldValue FROM table; 

should provide you with what you need.

ADDED TO BASED DEFINITIVE DATA

If you have data separated by \n values ​​from Field2 , then your query might become:

 SELECT SUBSTRING(COLUMN, LOCATE('Field1 : ', COLUMN)+9, 4) as Field1 SUBSTRING(COLUMN, LOCATE('Field2 : ', COLUMN)+9, LOCATE(CHAR(10), COLUMN) - LOCATE('Field2 : ', COLUMN)+9) as Field2, SUBSTRING(COLUMN, LOCATE(CHAR(10), COLUMN)+1) as RestOfData FROM table; 

Something still tells me that everything you return to this data should be able to parse better.

Answer 4

I think the simplest one is:

 SELECT SUBSTRING( Column, 10 ) AS Field1_value FROM table