CODEWITHSUNDEEP
sqloracle-database
NimChimpsky
NimChimpsky

Reputation: 47290

How to get first 4 digits in a number, with oracle sql

I could do 

select substr(to_char(20041111), 1, 4) FROM dual;

2004

Is there a way without converting to string first?

Upvotes: 2

Views: 24781

Answers (3)

vc 74
vc 74

Reputation: 38179

The following does not convert to a string but I'm not sure it's more readable...:

select floor(20041111 / power(10, floor(log(10, 20041111) - 3)))
from dual;

log(10, 20041111) -> 8.3... meaning that 10 ^ 8.3... = 20041111

if you floor this value, you get the number of digits in the base 10 representation of your number

if you want to remove digits, you just need to divide by 10 ^ (#digits - 3) (and not -4 since 10^1 already has 2 digits)

Upvotes: 3

Matt
Matt

Reputation: 15061

Another approach would be to use REGEXP_REPLACE

SELECT REGEXP_REPLACE(20041111,'....$') FROM dual;

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132580

You can use the FLOOR function:

select floor(20041111/10000) from dual;

Upvotes: 5

Related Questions