Remove Leading Zeros

Given data in a column which look like this: 00001 00 00026 00 I need to use SQL to remove anything after the space and all leading zeros from the values so that the final output

Solution 1:

This was tested on DB2 for Linux/Unix/Windows and z/OS.

You can use the LOCATE() function in DB2 to find the character position of the first space in a string, and then send that to SUBSTR() as the end location (minus one) to get only the first number of the string. Casting to INT will get rid of the leading zeros, but if you need it in string form, you can CAST again to CHAR.

SELECTCAST(SUBSTR(col, 1, LOCATE(' ', col) -1) ASINT)
FROM tab

Solution 2:

In DB2 (Express-C 9.7.5) you can use the SQL standard TRIM() function:

db2 =>CREATETABLE tbl (vc VARCHAR(64))
DB20000I  The SQL command completed successfully.
db2 =>INSERTINTO tbl (vc) VALUES ('00001 00'), ('00026 00')
DB20000I  The SQL command completed successfully.
db2 =>SELECTTRIM(TRIM('0'FROM vc)) AS trimmed FROM tbl

TRIMMED                                                         
----------------------------------------------------------------1262 record(s) selected.

The inner TRIM() removes leading and trailing zero characters, while the outer trim removes spaces.

Solution 3:

This worked for me on the AS400 DB2. The "L" stands for Leading. You can also use "T" for Trailing.

Solution 4:

I am assuming the field type is currently VARCHAR, do you need to store things other than INTs?

If the field type was INT, they would be removed automatically.

Alternatively, to select the values:

SELECT (CAST(CAST Col1 ASint) AS varchar) AS Col1

Solution 5:

I found this thread for some reason and find it odd that no one actually answered the question. It seems that the goal is to return a left adjusted field:

SELECTTRIM(L '0'FROM SUBSTR(trim(col) ||' ',1,LOCATE(' ',trim(col) ||' ') -1))
  FROM tab