Getting the ceil value of a number in SQLite

Question

So I see this question has a good answer, but I want to round up no matter what, instead of rounding down no matter what. Adding 1 to it before casting int wouldn't work because it would "round" 5.0 into 6.0, for example.

So how should I implement ceil in SQLite?

score 11 · Accepted Answer · answered Feb 19 '13 at 23:56

11

How about this?

select (case when x = cast(x as int) then cast(x as int)
             else 1 + cast(x as int)
        end)

answered Feb 19 '13 at 23:56

Gordon Linoff

1,242,037
58
646
786

I know this was a while ago but Gordon I think DomingoR's answer is probably the most succinct here. – Bruno May 31 '17 at 07:13
1

This one (although more verbose than DomingoR's answer) is actually correct. – tonypdmtr Nov 06 '17 at 22:10
This one is the correct approch since it covers all the corner cases. [Here](https://stackoverflow.com/a/54399937/7594961) is a more readable version. – Bertram Gilfoyle Jun 18 '19 at 11:23

DomingoR · Answer 2 · 2018-10-06T01:00:25.703

7

This will give you the same answer more elegantly:

SELECT CAST(x+1-1e-n AS INT);

(assuming you won't have a precision greater than n decimal points)

edited Oct 06 '18 at 01:00

answered Aug 21 '16 at 22:27

DomingoR

181
1
9

2

This will not round up as it will work as floor function. If x has decimal part below 0.5 then x+0.5 will not go over the next integer. This, however, will work: `cast(round(x+0.5) as int)` – tonypdmtr Nov 06 '17 at 22:29
1

The best way is SELECT CAST(x + 0.9 AS INT); – Hussein mahyoub Feb 16 '18 at 21:51
@Husseinmahyoub you solution would only work assuming that `x` would only take numbers with at most 1 decimal point. – DomingoR Apr 26 '18 at 12:20
3

nope, this is incorrect. Try it with x = 1... it'll give you 2 – Kyle Oct 04 '18 at 21:08
1

@Kyle thanks for nothing this. This should definitively work for all cases where where the precision is less than **n**: `SELECT CAST(x+1-1e-n AS INT);` – DomingoR Oct 06 '18 at 00:57
Hey guys, why do you say that @Husseinmahyoub answer doesn't work? I'm trying with android sqlite and it's working, even for 4+ decimal points. I'm asking to prevent future bugs – Luís Chaves Oct 09 '19 at 20:18
1

@Luís Chaves if you try x = 1.01 on his solution, you will get 1 while the solution should be 2. – DomingoR Oct 10 '19 at 16:41
Yeah got it! Thanks @DomingoR! – Luís Chaves Oct 10 '19 at 17:04
I'm getting Error: unrecognized token: "1e" – Walking Corpse Mar 27 '22 at 12:12
@HughDarling, the "1e-n" is equivalent to 0.001 (if n=3). Replacing this should work without issues. – DomingoR May 15 '22 at 05:18

score 0 · Answer 3 · edited Oct 20 '14 at 08:26

0

using php you can easily:

$db->createFunction('ceil', 'ceil', 1);

$db->createFunction('floor', 'floor', 1);

select ceil(\`column`) from table;

edited Oct 20 '14 at 08:26

Marcin Nabiałek

109,655
42
258
291

answered Oct 20 '14 at 08:00

user2812583

19

Agreed, this doesn't apply to pure SQLite – FinancialRadDeveloper Feb 01 '18 at 12:54
Although the language is php what I feel is that @user2812583 has actually proposed a valid solution. You can always create custom functions in SQLite like this but thankfully there are better approaches in this context. Still +1 from me for proposing something alternative. – Ayan Sengupta Apr 25 '18 at 05:53

score -2 · Answer 4 · edited Jan 03 '19 at 21:19

-2

Some useful answers, I found this solution really easy as well.

select round(column+0.5) from table;

It will mean you are always rounding up if your original number is < 0.5 or rounding back down if your original number is > 0.5

edited Jan 03 '19 at 21:19

Eric Brandt

7,886
3
18
35

answered Jan 03 '19 at 18:33

Darryl Usher

5
1

1

It won't work always. Take for example, `CEIL(1) = 1` where `ROUND(1+0.5, 0) = 2` Take a look at [this](https://stackoverflow.com/a/54399937/7594961) post – Bertram Gilfoyle Jan 31 '19 at 15:22
If x is 0 this will give 1 – Lorenzo Persichetti Aug 06 '19 at 10:18

Getting the ceil value of a number in SQLite

4 Answers4

Linked