Retrieve Data After A Specific Carriage Return In A Text Column

[juanrmendoza]

I have a text column that may contain carriage returns and I am trying to return the string after the 1st carriage return, 2nd, 3rd carriage return etc. 

 

I create a record in the table as follows:

 

line 1 plus this string A1234567890123

line 2 plus this string B12345678901234

line 3 plus this string C123456789012345

line 4 plus this string D1234567890123456

 

I tested with the following to see if I could locate the 1st string “line” and 2nd string “line” etc and I can get this work without any issue.

 

SELECT line_item_text_id, SUBSTRING(line_item_text FROM LENGTH(SUBSTRING_INDEX(line_item_text, ‘line’, 1))+1) 

FROM order_line_item_text

WHERE line_item_text_id = ‘630400’

AND line_item_text_code = ‘mark’

 

Returns 

line 1 plus this string A1234567890123

 

 

SELECT line_item_text_id, SUBSTRING(line_item_text FROM LENGTH(SUBSTRING_INDEX(line_item_text, ‘line’, 2))+1) 

FROM order_line_item_text

WHERE line_item_text_id = ‘630400’

AND line_item_text_code = ‘mark’

 

Returns 

line 2 plus this string A12345678901234

 

SELECT line_item_text_id, SUBSTRING(line_item_text FROM LENGTH(SUBSTRING_INDEX(line_item_text, ‘line’, 3))+1) 

FROM order_line_item_text

WHERE line_item_text_id = ‘630400’

AND line_item_text_code = ‘mark’

 

Returns 

line 3 plus this string A123456789012345

 

SELECT line_item_text_id, SUBSTRING(line_item_text FROM LENGTH(SUBSTRING_INDEX(line_item_text, ‘line’, 4))+1) 

FROM order_line_item_text

WHERE line_item_text_id = ‘630400’

AND line_item_text_code = ‘mark’

 

Returns 

line 4 plus this string A1234567890123456

 

I then tried to proceed with trying to base this on carriage returns and substituted  ‘line’ with char(13) no luck

I then tried to proceed with trying to base this on carriage returns and substituted  ‘line’ with char(13,10) no luck

I then tried to proceed with trying to base this on carriage returns and substituted  ‘line’ with ‘r” no luck

I then tried to proceed with trying to base this on carriage returns and substituted  ‘line’ with ‘rn” no luck

I then tried to proceed with trying to base this on carriage returns and substituted  ‘line’ with ‘+0x0D+0x0A+’no luck

I then tried to proceed with trying to base this on carriage returns and substituted  ‘line’ with ‘+0x0D+’no luck

 

someone told me that this was complex parsing for mysql

 

I am finding that hard to believe but I am no where near an expert at mysql and I am basically learning this the hard way by trial an error. 

 

Any assistance would be greatly appreciated

 

 

 

 

 

 

 

[peterlaursen]

I also believe that this should be possible using string functions available here https://dev.mysql.com/doc/refman/5.6/en/string-functions.html. With respect to string parsing I don’t think MySQL is less capable than other RDBMS

 

One thing I could suggest is to SELECT HEX .. and use the string function on the hexadecimal string identifying linebreaks here, and extracting the substring you need.  Next UNHEX. It can still be done in a single statement by nesting UNHEX and HEX like “SELECT UNHEX(.. HEX ..)”. But there may be simpler solutions.

[Author]

Posts