MYSQL select substring between two different delimiters

Select substring between two different delimiters

Hello together,

today i want to show you a single sql-command for separating a text between two delimiters.


phpmyadmin – recommended but not needed


You have a Text between 2 different delimiters in a datafield and you want only this text in the ouput.
For example:

table name=testtable
column name=text (varchar 80)
content of text=“This is a [Test] because i like it “

You want:

SELECT substr(text,Locate(‘[‘, text )+1,Locate(‘]’, text )-(Locate(‘[‘, text )+1)) FROM testtable

the substring function in mysql has the following structure:
substr(column name, position where to start, length)

Without variables, this would be the sql-command:
select substr(“This is a [Test] because i like it”, 12,4)

So, for the position of the 1st delimiter, we use the locate-funktion:
select locate( ‘[‘, “This is a [Test] because i like it” )

but we want to start with the next char, so we need to add 1:
select locate(‘[‘,”This is a [Test] because i like it”)+1

The Result is: 12
For the length we need the difference between the 2nd delimiter and the 1st char of our text:
select locate(‘]’, “This is a [Test] because i like it” )-(Locate(‘[‘, “This is a [Test] because i like it” )+1)

The Result is: 4

Bye Bye