Solved

mySQL syntax help for a query

Posted on 2014-04-09
5
348 Views
Last Modified: 2014-04-09
I have lots of text in a column (mediumtext) with lots of rows.  I need to find the position of a substring like:
C82564F1:0008A56C
where
- the string is always that length, 17 bytes
- it always includes a colon in the 9th position
- each of the 16 other characters are a 0 to 9 or A to F (i.e. a HEX)
- it won't have a /n or /t or any other special characters in it
Can I do this with a single query or is there an efficient way to do that with a function?
Thank you
0
Comment
Question by:Francois Koutchouk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39990045
Try this:
SELECT column FROM table WHERE column REGEXP '[A-F0-9]{8}:[A-F0-9]{8}'

Open in new window

HTH,
Dan
0
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points
ID: 39990127
Looking at the MySQL functions that are available, I can't see anything that provides the position of the matched text, unfortunately, unless you can guarantee there are a limited number of : characters in the text.

Dan's solution will return the rows that match, but can't provide the position or the part of the text that matched.

Otherwise, you may need to create a user defined function to achieve this. It looks like there are some regexp UDF's that might work for you though: https://github.com/hholzgra/mysql-udf-regexp

Let us know if you need help implementing these (not sure if I can help, but someone should be able to).
0
 
LVL 2

Author Comment

by:Francois Koutchouk
ID: 39990144
Beautiful!!! Thank you.
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39990846
No need for that Terry.

Can the OP please post the result of compiling UDF-regexp with a modern (>5.5) MySQL install?
It's over 7 years old so it would be nice to know if it's still usable.
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question