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 will be:

1
26

How can I best do this?

Btw I'm using DB2

回答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.

SELECT CAST(SUBSTR(col, 1, LOCATE(' ', col) - 1) AS INT)
FROM tab

回答2:

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

db2 => CREATE TABLE tbl (vc VARCHAR(64))
DB20000I  The SQL command completed successfully.
db2 => INSERT INTO tbl (vc) VALUES ('00001 00'), ('00026 00')
DB20000I  The SQL command completed successfully.
db2 => SELECT TRIM(TRIM('0' FROM vc)) AS trimmed FROM tbl

TRIMMED                                                         
----------------------------------------------------------------
1                                                               
26                                                              

  2 record(s) selected.

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

回答3:

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 AS int) AS varchar) AS Col1

回答4:

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

回答5:

One option is implicit casting: SELECT SUBSTR(column, 1, 5) + 0 AS column_as_number ...

That assumes that the structure is nnnnn nn, ie exactly 5 characters, a space and two more characters.

Explicit casting, ie SUBSTR(column,1,5)::INT is also a possibility, but exact syntax depends on the RDBMS in question.

回答6:

Use the following to achieve this when the space location is variable, or even when it's fixed and you want to make a more robust query (in case it moves later):

SELECT CAST(SUBSTR(LTRIM('00123 45'), 1, CASE WHEN LOCATE(' ', LTRIM('00123 45')) <= 1 THEN LEN('00123 45') ELSE LOCATE(' ', LTRIM('00123 45')) - 1 END) AS BIGINT)

If you know the column will always contain a blank space after the start:

SELECT CAST(LOCATE(LTRIM('00123 45'), 1, LOCATE(' ', LTRIM('00123 45')) - 1) AS BIGINT)

both of these result in:

123

so your query would

SELECT CAST(SUBSTR(LTRIM(myCol1), 1, CASE WHEN LOCATE(' ', LTRIM(myCol1)) <= 1 THEN LEN(myCol1) ELSE LOCATE(' ', LTRIM(myCol1)) - 1 END) AS BIGINT)
FROM myTable1

This removes any content after the first space character (ignoring leading spaces), and then converts the remainder to a 64bit integer which will then remove all leading zeroes.

If you want to keep all the numbers and just remove the leading zeroes and any spaces you can use:

SELECT CAST(REPLACE('00123 45', ' ', '') AS BIGINT)

While my answer might seem quite verbose compared to simply SELECT CAST(SUBSTR(myCol1, 1, 5) AS BIGINT) FROM myTable1 but it allows for the space character to not always be there, situations where the myCol1 value is not of the form nnnnn nn if the string is nn nn then the convert to int will fail.

Remember to be careful if you use the TRIM function to remove the leading zeroes, and actually in all situations you will need to test your code with data like 00120 00 and see if it returns 12 instead of the correct value of 120.

回答7:

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:

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