Leading Zeros

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • rawlund
    New Member
    • Jan 2008
    • 1

    #1

    Leading Zeros

    Am queying table using this statement
    [CODE=mysql]select max(SUBSTRING (policyno,11,6) ) as policyno from table where substring(polic yno,5,3) = 070[/CODE]

    The statement works but it does not return the maximum value where policyno has leading zeros. Example of policyno is TM1/070/1/009899/2008

    Want to remove 009899 substring and get the maximum value from the table then increment by one. It works only when the number has no leading zeros like 9899

    Any help will be appreciated
    Last edited by mwasif; Jan 24 '08, 06:00 PM. Reason: Added [code=mysql]/[/code] tags
  • mwasif
    Recognized Expert Contributor
    • Jul 2006
    • 802

    #2
    Hi rawlund,

    Welcome to TSDN!!!

    You need to convert the value to INTEGER and then use it for calculation e.g.
    [CODE=mysql]SELECT max(CAST(SUBSTR ING(policyno,11 ,6) AS SIGNED )) as policyno
    FROM table WHERE SUBSTRING(polic yno,5,3) = 070[/CODE]

    Comment

    Working...