{"id":29280,"date":"2023-02-08T16:41:48","date_gmt":"2023-02-08T06:41:48","guid":{"rendered":"https:\/\/database.guide\/?p=29280"},"modified":"2023-02-08T16:42:12","modified_gmt":"2023-02-08T06:42:12","slug":"sqlite-abs-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/sqlite-abs-explained\/","title":{"rendered":"SQLite ABS() Explained"},"content":{"rendered":"\n<p>In SQLite, <code>ABS()<\/code> is a built-in scalar function that returns the absolute value of its argument. <\/p>\n\n\n\n<p>The absolute value is the non-negative equivalent of the argument. It can be thought of as the distance from zero that the number resides on the number line, without considering direction. <\/p>\n\n\n\n<!--more-->\n\n\n\n<p>For example, if the argument is <code>-8<\/code> the absolute value is <code>8<\/code>. If the argument is <code>8<\/code>, the absolute value is also <code>8<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Syntax<\/h2>\n\n\n\n<p>The syntax goes like this: <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>abs(X)<\/code><\/pre>\n\n\n\n<p>Where <code><em>X<\/em><\/code> is the number in question. <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>If <code><em>X<\/em><\/code> is a string or blob that cannot be converted to a numeric value, <code>abs()<\/code> returns <code>0.0<\/code>. <\/li>\n\n\n\n<li>If <code><em>X<\/em><\/code> is <code>NULL<\/code>, then <code>abs()<\/code> returns <code>NULL<\/code>.<\/li>\n\n\n\n<li>If <em><code>X<\/code><\/em> is the integer <code>-9223372036854775808<\/code> then <code>abs()<\/code> throws an integer overflow error. This is because there is no equivalent positive 64-bit two complement value.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Example<\/h2>\n\n\n\n<p>Here&#8217;s an example of how <code>abs()<\/code> works:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT abs(-75);<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">75<\/pre>\n\n\n\n<p>We get the same result if the number is positive:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT abs(75);<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">75<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Non-Numeric Argument<\/h2>\n\n\n\n<p>If the argument is a string or blob that cannot be converted to a numeric value, <code>abs()<\/code> returns <code>0.0<\/code>. <\/p>\n\n\n\n<p>Example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT abs('Negative Ten');<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">0.0<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">NULL Argument<\/h2>\n\n\n\n<p>If the argument is <code>NULL<\/code>, then <code>abs()<\/code> returns <code>NULL<\/code>.<\/p>\n\n\n\n<p>First, let&#8217;s <a href=\"https:\/\/database.guide\/nullvalue-show-null-values-in-sqlite\/\" data-type=\"post\" data-id=\"24797\">set SQLite to show NULL values<\/a>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>.nullvalue null<\/code><\/pre>\n\n\n\n<p>Now let&#8217;s run <code>abs()<\/code> with a NULL value:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT abs( NULL );<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">null<\/pre>\n\n\n\n<p>The reason I started with <code>.nullvalue null<\/code> is because, by default, the SQLite command line returns an empty string for NULL values. By specifying <code>.nullvalue null<\/code>, we were able to see the word <code>null<\/code> whenever we encounter NULL values.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Error on Large Numbers<\/h2>\n\n\n\n<p>If the argument is the integer <code>-9223372036854775808<\/code> then <code>abs()<\/code> throws an integer overflow error. This is because there is no equivalent positive 64-bit two complement value:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT abs( -9223372036854775808 );<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Runtime error: integer overflow<\/pre>\n\n\n\n<p>Passing the positive equivalent doesn&#8217;t return an error, but it returns the result in scientific notation:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT abs( 9223372036854775808 );<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">9.22337203685478e+18<\/pre>\n\n\n\n<p>If we reduce the value by 1, then we&#8217;re fine with both numbers:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n    abs( -9223372036854775807 ) AS Negative,\n    abs( 9223372036854775807 ) AS Postive;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Negative             Postive            \n-------------------  -------------------\n9223372036854775807  9223372036854775807<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Missing Argument<\/h2>\n\n\n\n<p>Calling <code>abs()<\/code> without an argument results in an error:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT abs();<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Parse error: wrong number of arguments to function abs()\n  SELECT abs();\n         ^--- error here<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>In SQLite, ABS() is a built-in scalar function that returns the absolute value of its argument. The absolute value is the non-negative equivalent of the argument. It can be thought of as the distance from zero that the number resides on the number line, without considering direction.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[100],"tags":[20],"class_list":["post-29280","post","type-post","status-publish","format-standard","hentry","category-sqlite","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/29280","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=29280"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/29280\/revisions"}],"predecessor-version":[{"id":29285,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/29280\/revisions\/29285"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=29280"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=29280"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=29280"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}