CODEWITHSUNDEEP
mysqlregex
Surya Gupta
Surya Gupta

Reputation: 165

How to use the regular expression in mysql query

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...

Upvotes: 3

Views: 7113

Answers (1)

user
user

Reputation: 6947

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.

Upvotes: 5

Related Questions