CODEWITHSUNDEEP
mysqlsqlsubstring
DarkAjax
DarkAjax

Reputation: 16223

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

I have a MySQL table with just a single text type column with lots of information in the same format, starting with a string like this:

Field1 : 1234
Field2 : Something

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

What I need is to get the just the values of what's after Field1 and Field2 (in this case 1234 and Something) on a Query, the value after Field1 is easy because it's always 4 characters long, but the problem is the one after Field2 because it's size varies for each record, what I have so far is this:

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

I know after Field2's value there's a line break, so I'll think of considering the \n character to delimit the value I need.

How do I get this substring of variable size for each row?

P.S. Yes the data is horribly structured, I'm not able to change it...

Upvotes: 4

Views: 7403

Answers (4)

Karlson
Karlson

Reputation: 3048

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

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

should give you what you need.

ADDED BASED ON CLARIFIED DATA

If you actually have data separated from Field2 value by \n then your query can 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 whatever you return this data into should be able to parse it better.

Upvotes: 1

DarkAjax
DarkAjax

Reputation: 16223

Well, finally I got it (thanks for your contributions everybody), this is the final resulting Query:

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 strings "Field1 : " and "Field2 : ", this isolates those 2 strings and their following values from the rest of column's text, and then I use a substring (knowing that the value after Field1 is always 4 characters long helps), and the 15 in the substring is to ensure I look not for the second line break.

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

I think the simplest is:

SELECT SUBSTRING( Column, 10 ) AS Field1_value
FROM table

Upvotes: 1

aingram
aingram

Reputation: 446

Supply a dynamic value as the 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 single-digit X in FieldX.

Upvotes: 1

Related Questions