Search code examples
sqlnetezza

SQL order of function explanation for trim, replace and left trim

Good day,

I found a very useful piece of code in Netezza SQL that removes leading zeroes from ID numbers. I however don't fully understand the logical processing in SQL behind it. Could someone please assist in explaining how SQL goes about processing the line of code below? I understand that one reads from the inside out but the section between the LTRIM and the second replace statement confuses me.

SUBSTR(REPLACE(LTRIM(REPLACE(TRIM(ID_NO),'0',' ')),' ','0'), 1, 13)

Thank you kindly.

Solution

  • LTRIM works with whitespace. So this code

    a) Removes any surrounding whitespace (TRIM)

    b) Replaces 0s with whitespace (inner REPLACE),

    c) LTRIMs the string (which will only remove leading whitespace, which must have originally been 0s because of (a))

    d) Replaces the whitepaaces back to 0s (outer REPLACE)

    e) (not really relevant here) then does a SUBSTRING to the result.

    The overall effect is, for those 0s at the start of the string, they're removed. All other 0s are not affected (whilst pretending to be spaces) by the LTRIM.