?
Solved

mySQL syntax help for a query

Posted on 2014-04-09
5
Medium Priority
?
354 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
  • 2
4 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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

599 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