CODEWITHSUNDEEP
sqlsql-serveroracle-database
Shawn
Shawn

Reputation: 5260

truncate integer

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.

Upvotes: 1

Views: 886

Answers (2)

Jamiec
Jamiec

Reputation: 136094

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

Upvotes: 2

bumble_bee_tuna
bumble_bee_tuna

Reputation: 3563

You could use case statements for this like: 

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

Upvotes: 2

Related Questions