Gentlemen;

Although I've been actually programming with MySQL for a number of years with good results I am far, far, far from a DB expert! I have a new requirement that I cannot figure out an acceptable way to achieve. I thought that some "seasoned" db person(s) would have a better idea of how I could accomplish this.

Here are some sample specs:

table column definition:

_serial varchar(60) default ' '

table row entry values for column _serial:
string value consists of 1 to n "sets" of 2-digit numbers.

1: 0002030405
2: 0102030405
3: 0103040607
4: 0203040506
5: 0004050708
6: 0400020106
7: 00060100
8: 0103050608
9: 0402101200

Search Arg:

020410

Criteria:

This argument would ONLY match row 9 even though
the number sets are not in sequence. Also note
that the number set "10" is requested and although
it appears on other rows it is NOT actually a SET
in the other rows (it is made up of one digit from
one set and another from the adjacent set.

Task:

Write a SELECT statement that will accomplish, if possible, the above criteria


I was planning on utilizing the INSTR() function either on the entire string or perhaps splitting up the argument string into 2-digit "sets" and AND'ing multiple INSTR() together but that wouldn't address the non-aligned set values (i.e.: the "set" 10).

Any help, ideas, recommendations, suggestions will be very much appreciated as I pond this problem.

Thank you,

Bob

An idea,

U can use regex
SELECT id,_serial, if("10" REGEXP ('^([0-9][0-9])*'),"Yes","No") FROM strings
... jut some tests, not final solution (im a begginer)

or if u can build a string_format_function() that outputs the formated string
SELECT string_format_function(_serial) From table

Result "00,12,40,10,25,01,01"
then using same function to test the search string
WHERE string_format_function(_serial) LIKE string_format_function(_search_string)

something like this function
FORMAT(X,D)
Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.

only put comma after 2 digits.

i'm curious about the final solution