How to use the regular expression in mysql query

Question

i have following entry in the database:

id       summary
1       numbers of word 200 in the doc 0x123
2       [main] numbers of word 150 in the doc 0x678
3       numbers of word 678 in the doc 0x4536 
4       this file has empty
5       not completed
6       numbers of word 86542 in the doc 0x6356

I want to retrieve the id from the database "Page" and table name "details" with respect to summary value. i want all the four id (1,2,3,6) with summary value "numbers of word <any_number> in the doc <any_number> And manage with the spaces also...

user · Accepted Answer · 2012-12-06 10:05:58Z

If you can accept the tradeoff that % also matches non-digits, this is trivial using the LIKE SQL operator, which is also cross platform.

select * from details where lower(summary) LIKE 'numbers of word % in the doc %'

If you only want to match digits and spaces, I think you do need regular expressions, which thankfully are directly supported in MySQL. MySQL 5.1 manual, section 12.5.2: Regular Expressions. Something like this:

select * from details
where lower(summary) REGEXP '^numbers of word [0-9 ]+ in the doc [0-9x ]+$'

If you don't need or want case insensitivity you can do away with the call to LOWER(). I'm including x in the second regular expression because your numbers are prefixed with 0x (hexadecimal?).

Do keep in mind that indexes likely are not usable with LIKE or REGEXP which means that the query will incur a comparatively very large I/O cost.

Collectives™ on Stack Overflow

How to use the regular expression in mysql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related