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.

Conditions:

mysql
phpmyadmin – recommended but not needed

Problem:

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:
ouput=“Test”

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

Explanation:
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
Chris

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>