SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict bimalpoudel's Avatar
    Join Date
    Feb 2009
    Location
    Kathmandu, Nepal
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    REGEX capture in SELECT statement

    I have a special case of capturing by regular expression in the SELECT statement.
    This is the background: In a poorly designed database, a user might have stored the
    amount as the following record:

    amount (varchar)
    ---------
    $7,232.05
    7,232.05$
    7,232.05 only
    7,232.05
    7232.05

    I would like to capture the data with the digits and a period only.
    And all the fields should be having same output in this case as:

    amount (numeric/decimal/float)
    ---------
    7232.05
    7232.05
    7232.05
    7232.05
    7232.05

    My ideal query would be something like:
    Code:
    SELECT
    	amount REGEXP '/[\d\.]+/is' AS amount
    FROM transactions;
    What can be the real alternative to this problem?
    It is not a matching for a condition, but the data extraction:
    extracting the digits and a period.

    By this way, I can even sort the records numerically (low to high, or high to low) with results as expected.

    Here were some other issues on regular expressions, a bit different than mine:
    * http://www.brainbell.com/tutorials/M...xpressions.htm
    * http://www.experts-exchange.com/Data..._26582728.html
    * http://www.phpbuilder.com/board/arch...-10334908.html

    Thank you for your time in searching for a solution.
    Last edited by bimalpoudel; Jan 23, 2011 at 12:43. Reason: issues highlighted
    Bimal Poudel @ Sanjaal Framework over Smarty Template Engine
    ASKING INTERESTING QUESTIONS ON SITEPOINT FOURM

    Hire for coding support - PHP/MySQL

  2. #2
    SitePoint Addict bimalpoudel's Avatar
    Join Date
    Feb 2009
    Location
    Kathmandu, Nepal
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Further, CAST is not a solution.
    I don't want to use PHP calculations, because I need to sort the data at the query level.
    Bimal Poudel @ Sanjaal Framework over Smarty Template Engine
    ASKING INTERESTING QUESTIONS ON SITEPOINT FOURM

    Hire for coding support - PHP/MySQL

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    since mysql's regexp can find strings but cannot replace them, the best you can do in the query is...
    Code:
    ORDER
        BY CAST(
           REPLACE(
           REPLACE(
           REPLACE(
           REPLACE(amount,'$','')
                         ,',','') 
                         ,'.','') 
                         ,' ','') 
                         ,'only','') 
               AS UNSIGNED)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict bimalpoudel's Avatar
    Join Date
    Feb 2009
    Location
    Kathmandu, Nepal
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Poor database design --> poor solution. I really agree with you!
    This was one of the real life case I have ever seen.

    Even something like this in my mind:
    Build all possible non-numerical records in each rows.
    DISTINCT them in a temp zone.
    Build the list of REPLACE dynamically.
    Build the full SQL.

    Just because, you cannot predict what could have been stored in the VARCHAR column.
    Sometimes, we have to do the wrong way though we know it.
    Bimal Poudel @ Sanjaal Framework over Smarty Template Engine
    ASKING INTERESTING QUESTIONS ON SITEPOINT FOURM

    Hire for coding support - PHP/MySQL

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you have the option to convert this poor design to a better one or is it one of those "do not change anything, but please make it work" projects? I would promptly upgrade this varchar to NUMERIC storage (I'm not saying I'd just change the data type of the column of course, but do a proper re-import of the data).

  6. #6
    SitePoint Addict bimalpoudel's Avatar
    Join Date
    Feb 2009
    Location
    Kathmandu, Nepal
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No rights to modify the data/database.
    Option to re-import really helps; but not allowed.
    Bimal Poudel @ Sanjaal Framework over Smarty Template Engine
    ASKING INTERESTING QUESTIONS ON SITEPOINT FOURM

    Hire for coding support - PHP/MySQL


Bookmarks

Posting Permissions

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