Results 1 to 6 of 6

Thread: TRIM Issue

  1. #1
    Join Date
    Oct 2012
    Posts
    17

    Unanswered: TRIM Issue

    I have a field in a table that has some values with leading zero(s). I want to remove these zeros (whether it's 1 or 5 of them).

    This is the field:

    Code:
    supplier_reference varchar(32) DEFAULT NULL
    This is what I'm trying:

    PHP Code:
    mysql_query('UPDATE tmp_price_compare SET tmp_price_compare.supplier_reference = TRIM(LEADING '0' FROM tmp_price_compare.supplier_reference)
        or die(mysql_error()); 
    No dice. Doesn't work.

    Any ideas?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It seems that quotation markes were not paired.

  3. #3
    Join Date
    Oct 2012
    Posts
    17
    Nice catch. Thanks.

    So now:

    Code:
    mysql_query('UPDATE tmp_price_compare SET tmp_price_compare.supplier_reference = TRIM(LEADING '0' FROM tmp_price_compare.supplier_reference')
    	or die(mysql_error());
    doesn't work.

    Ideas? Does it have something to do with the result being a varchar?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    mysql_query('U... ...G '0' F... ...e') or die(mysql_error());
    Last edited by tonkuma; 04-16-13 at 10:09.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by rseigel View Post
    Nice catch. Thanks.

    So now:

    Code:
    mysql_query('UPDATE tmp_price_compare SET tmp_price_compare.supplier_reference = TRIM(LEADING '0' FROM tmp_price_compare.supplier_reference')
    	or die(mysql_error());
    doesn't work.

    Ideas? Does it have something to do with the result being a varchar?
    no its has everything to do with unmatched pairs of quotes
    PHP Code:
    mysql_query("UPDATE tmp_price_compare SET tmp_price_compare.supplier_reference = TRIM(LEADING '0' FROM tmp_price_compare.supplier_reference")
        or die(
    mysql_error()); 


    you will make life easier for yourself if you assign the SQL to a variable and then use that variable wherever
    the reason?
    well it makes debugging easier
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2012
    Posts
    17
    Tried that - thanks.

    Get the following error:

    HTML Code:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
    In case it matters:

    PHP version 5.4.13
    MySQL version 5.5.30-log

Posting Permissions

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