Looking for tokens inside a string

[Aurelien Mazurie]

Hello to all,
I'm trying to do a weird (?) thing with regexp and MySQL. For example,
i have a column with:

token1|token2|token3

(three tokens separated by pipes)
I'm trying to write an SQL query in order to look for the presence of
one of these tokens, that is "look for a string like 'tokenX' that is
preceded by the beginning of the line OR a pipe, AND that is followed
by the end of the line OR a pipe". I have tried this:

SELECT * FROM ... WHERE (... REGEXP "(^|\|)tokenX($|\|)");

but, of course, it doesn't work. So my question is: what's wrong ? My
regular expression, or my idea ?? There is a another way to obtain
what i want, that is to select a token within a string when tokens are
separated by anything but a comma ?

If anyone have an answer, =)

Serial

[John]

Hi, Aurelien:

IMO, the answer depends on what rules you have governing the tokens.
For example, if you used literally the values "token1", "token2", and
"token3", there is no need to consider the pipes at all. Just use:

COLUMN LIKE '%token1%'

to look for token1 - it will not match any other tokens. Repeat for the
other tokens. If, however you are using tokens like "aaa" and "aaaaaa",
then you cannot use this method because "aaa" will also match inside of
"aaaaaa". In that case, you would have to do something like this to
find "aaa":

COLUMN LIKE 'aaa|%' or COLUMN LIKE '%|aaa' or COLUMN LIKE '%|aaa|%' OR
COLUMN = 'aaa"

I'd opt for the first method if possible, because as you can see it is
more efficient.

Hope that helps,
John
Aurelien Mazurie wrote:

Hello to all,
I'm trying to do a weird (?) thing with regexp and MySQL. For example,
i have a column with:

token1|token2|token3

(three tokens separated by pipes)
I'm trying to write an SQL query in order to look for the presence of
one of these tokens, that is "look for a string like 'tokenX' that is
preceded by the beginning of the line OR a pipe, AND that is followed
by the end of the line OR a pipe". I have tried this:

SELECT * FROM ... WHERE (... REGEXP "(^|\|)tokenX($|\|)");

but, of course, it doesn't work. So my question is: what's wrong ? My
regular expression, or my idea ?? There is a another way to obtain
what i want, that is to select a token within a string when tokens are
separated by anything but a comma ?

If anyone have an answer, =)

Serial

[John]

Hi, Aurelien:

IMO, the answer depends on what rules you have governing the tokens.
For example, if you used literally the values "token1", "token2", and
"token3", there is no need to consider the pipes at all. Just use:

COLUMN LIKE '%token1%'

to look for token1 - it will not match any other tokens. Repeat for the
other tokens. If, however you are using tokens like "aaa" and "aaaaaa",
then you cannot use this method because "aaa" will also match inside of
"aaaaaa". In that case, you would have to do something like this to
find "aaa":

COLUMN LIKE 'aaa|%' or COLUMN LIKE '%|aaa' or COLUMN LIKE '%|aaa|%' OR
COLUMN = 'aaa"

I'd opt for the first method if possible, because as you can see it is
more efficient.

Hope that helps,
John
Aurelien Mazurie wrote:

Hello to all,
I'm trying to do a weird (?) thing with regexp and MySQL. For example,
i have a column with:

token1|token2|token3

(three tokens separated by pipes)
I'm trying to write an SQL query in order to look for the presence of
one of these tokens, that is "look for a string like 'tokenX' that is
preceded by the beginning of the line OR a pipe, AND that is followed
by the end of the line OR a pipe". I have tried this:

SELECT * FROM ... WHERE (... REGEXP "(^|\|)tokenX($|\|)");

but, of course, it doesn't work. So my question is: what's wrong ? My
regular expression, or my idea ?? There is a another way to obtain
what i want, that is to select a token within a string when tokens are
separated by anything but a comma ?

If anyone have an answer, =)

Serial