Solved

mySQL syntax help for a query

Posted on 2014-04-09
5
346 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Server group by 10 45
SQL query and VBA 5 46
issue with DB import 1 19
SQL Server show list of tables from a database with a parameter for the Database name 18 20
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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 …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

830 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