CODEWITHSUNDEEP
sqldb2
Bob
Bob

Reputation: 885

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

Upvotes: 5

Views: 43770

Answers (7)

bek
bek

Reputation: 711

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

Upvotes: 1

D. Kermott
D. Kermott

Reputation: 1673

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

enter image description here

Upvotes: 2

pilcrow
pilcrow

Reputation: 58651

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.

Upvotes: 3

bhamby
bhamby

Reputation: 15469

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

Upvotes: 4

Seph
Seph

Reputation: 8703

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.

Upvotes: 0

Cory Dolphin
Cory Dolphin

Reputation: 2670

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

Upvotes: 1

RET
RET

Reputation: 9188

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.

Upvotes: 0

Related Questions