CODEWITHSUNDEEP
sqldb2
Simon
Simon

Reputation: 1605

SQL query for first digits of a numeric field

I have a NUMERIC(5, 0) field in a IBM db2 database from which I only want the first 4 digit. I haven't been able to achieve what I want with using CAST.

How can I achieve this in SQL without casting as a String and using substring ?

Ex: 13525 --> 1352

Thanks for your help!

Upvotes: 0

Views: 3691

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Why not cast as a string and use substring?

You can also do:

select (case when field >= 10000 then floor(field / 10) else field end)

This assuming that if the field has 1234, then you want 1234 rather than 0123.

EDIT:

You can also use a string by using two calls to cast():

select cast(left(cast(field as varchar(5)), 4) as numeric(4, 0))

I should also note that in many databases, you can just do:

select left(field, 4)

and the database will do the appropriate conversions. I don't have DB2 nearby to check this.

Upvotes: 5

Related Questions