Geek asks and answers

MySQL REGEX using SUBSTRING with string length?

Let's say I have this data:

First line of text etc etc
Second line of text here

I want to return the first line, and I know how it starts, but not how it ends, only if it ends with a line break. This is what I still have:

SELECT
SUBSTRING(`file`, LOCATE('First line', `file`), LOCATE('First line', `file`)+10)
FROM `test`

This works, except +10it will not always be +10. The only marker I need to know where to stop is a new line, but I don’t think you can use the regex in SUBSTRING. Is there any way to do this?

+5
source share
2 answers

try the following:

SELECT 
      SUBSTRING( `file`, LOCATE('First line', `file`),
          LOCATE("\n",`file`) 
 FROM `test`
0
source

Try the following:

SELECT SUBSTRING(`file`, 1, LOCATE("\n", `file`) - 1) FROM `test`;

Substitution starts from one, not from zero. Subtracting one of the LOCATE result eliminates the return of the string.

0

More articles:


All Articles