CODEWITHSUNDEEP
mysql
Mamouz
Mamouz

Reputation: 23

How to find entries matching a specific string

I have more than 50000 records in my table with two columns (id and basic) looks like this

ID BASIC
1 XXX111XXX111
2 XXXX22221111
3 111XXXXX2212
4 2X1X212X1X1X
5 X21X12X1X12X

What I need is to display only records that consist of 5 "X" that are not adjacent. for example, from the above records i need to get data like this

ID BASIC
4 2X1X212X1X1X
5 X21X12X1X12X

What query will suite to retrieve such record from my database.

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

I interpret this as meaning that you want 5 Xs that are not adjacent. I think this does what you want:

where concat(' ', col, ' ') regexp '([^X]+[X]){5}[^X]'

The concat() just takes care of the situation where the first or last character is an "X".

Upvotes: 2

Related Questions