Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

mySQL syntax help for a query

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
Francois Koutchouk
Asked:
Francois Koutchouk
  • 2
1 Solution
 
Dan CraciunIT ConsultantCommented:
Try this:
SELECT column FROM table WHERE column REGEXP '[A-F0-9]{8}:[A-F0-9]{8}'

Open in new window

HTH,
Dan
0
 
Terry WoodsIT GuruCommented:
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
 
Francois KoutchoukCTOAuthor Commented:
Beautiful!!! Thank you.
0
 
Dan CraciunIT ConsultantCommented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now