regular expression in mysql query

Question

I have mysql table values like

1-2

1-2-3

1-4-5

1-4-5-12-15

15-45-75

1-5-15-25-35-55

55-65-75

I want to select which rows have number 5(not 15 or 25 or 35).

I have tried with LIKE query but it gives all 5 value contains rows(including 15, 35, 45,55).

SELECT ... WHERE linkage LIKE '%5%'

I think we can do it through REGEXP. Can you help me for it?

Regular expressions are terribly inefficient in every dbms out there. Don't do it. Just use a simple query that can take advantage of your indexes. — dodexahedron
– dodexahedron, Commented Aug 31, 2012 at 6:33

Community · Accepted Answer · 2020-06-20 09:12:55Z

7

You can do this using FIND_IN_SET:

SELECT ... 
WHERE FIND_IN_SET(5, REPLACE(linkage,'-',','));

Example @ sqlfiddle

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 31, 2012 at 6:29

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe G Joseph · Accepted Answer · 2012-08-31 06:51:36Z

2

try

SELECT ... WHERE concat('-',linkage,'-') LIKE '%-5-%'

SQL Fiddle Demo

edited Aug 31, 2012 at 6:51

answered Aug 31, 2012 at 6:28

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

5 Comments

dodexahedron Over a year ago

5 could appear at the start, middle, or end of the set. You need to match all three cases. Consider these values: 1-5-2, 5-2-1, 1-2-5. The edit I submitted will cover these.

Joe G Joseph Over a year ago

@dodexahedron: I am concatinating,a '-' at both ends of the column.. please check the updated answer

dodexahedron Over a year ago

That makes indexes useless, though. Shorter SQL is not always better SQL. While your query will certainly do it, it's better to do the ORs with the explicit cases, because one of the cases can use an index scan and the others will be table scans. Yours should result in a table scan every time. Worth testing to see how the query optimizer runs it, but I have a feeling the naive way of ORs for each of the 3 cases will outperform.

Joe G Joseph Over a year ago

@dodexahedron: Good thinking.. but the moment we use a % at the beginning of the search string , index will not be used.. so query with OR also will do table scan..

dodexahedron Over a year ago

You're absolutely correct. Chalk it up to trying to think at 3AM... There's no way to do what he wants without a table scan, so your example is probably the simplest. Call me crazy, but I don't trust that FIND_IN_SET function to be better than your solution. ;)

Collectives™ on Stack Overflow

regular expression in mysql query

2 Answers 2

Example @ sqlfiddle

Comments

SQL Fiddle Demo

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Example @ sqlfiddle

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related