MySQL substring from a newline

Question

Is there a simple way to only retrieve the info on a new line in a string? Example:

Some kind of info

Message from somewhere

ref code

Only need to get the second line, so "Message from somewhere"

have done it so have with a mix of substring_index, substring and instr but guessing they must a much easier way for it to be done???? Thanks

https://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql — Taha Paksu, Nov 10 '17 at 13:20

score 2 · Answer 1 · answered Nov 10 '17 at 13:30

You can indeed use SUBSTRING_INDEX for that.
But you need to nest the SUBSTRING_INDEX functions

Create table/insert data.

CREATE TABLE test (
  message TEXT
);

INSERT INTO test (message) VALUES("Some kind of info

Message from somewhere

ref code
");

Query

SELECT
  #or you might need to use '\r\n' instead of '\n'
  SUBSTRING_INDEX(SUBSTRING_INDEX(message, '\n', 3), '\n', -1)
FROM
 test

Result

SUBSTRING_INDEX(SUBSTRING_INDEX(message, '\n', 3), '\n', -1)  
--------------------------------------------------------------
Message from somewhere

1 Answers1