Solved

mySQL syntax help for a query

Posted on 2014-04-09
5
341 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:FKoutchouk
  • 2
5 Comments
 
LVL 34

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 1

Author Comment

by:FKoutchouk
ID: 39990144
Beautiful!!! Thank you.
0
 
LVL 34

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now