truncate integer

Question

I got a task to chop an integer by 1 digit. For example, If the new limit is 3 digits then 1234 will become 999 after the change. if the before change value is 12345 then it should becomes 999 after changes. if the pre-change value is 564 then it will remain unchanged.

This has to be done on Oracle as well as SQL server. the truc function only truncates decimal but not integer.

What is the best way to do this in SQL, PL/SQL or T-SQL?

Thanks in advance.

@Jamiec: Max 3 digit number is always `999` – juergen d Jun 11 '15 at 16:04 — juergen d, Jun 11 '15 at 16:04

score 2 · Answer 1 · answered Jun 11 '15 at 16:06

2

You could use case statements for this like:

SELECT CASE [yourInt] >= 1000 THEN 999 ELSE [yourInt] END AS 'UpperLimit'
From [YouTable]

answered Jun 11 '15 at 16:06

bumble_bee_tuna

3,533
7
43
83

score 2 · Accepted Answer · edited May 23 '17 at 10:26

2

This works for T-SQL. Converting it to other sql dialects should just be as simple as finding the similar methods

declare @numDigits INT = 3;
declare @maxNumber INT = POWER(10,@numDigits)-1 -- gets "999" when using 3 digits, 9999 when using 4 etc


DECLARE @input INT = 1234
DECLARE @output INT = IIF(@input>@maxNumber,@maxNumber,@input)

SELECT @output -- selects 999

Oracle does have the POWER function, but does not have the ternary/IIF function

edited May 23 '17 at 10:26

Community

1
1

answered Jun 11 '15 at 16:09

Jamiec

133,658
13
134
193

Hi, Jamiec and bumble_bee_tuna, thank you all so much for the clever ideas. I appreciate it. – Shawn Jun 11 '15 at 16:19

truncate integer

2 Answers2