Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006

    Unanswered: Please assist with complex (to me) select conditions?


    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:



    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.


    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,


  2. #2
    Join Date
    Dec 2004
    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
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts