{"id":24367,"date":"2022-03-16T08:34:44","date_gmt":"2022-03-15T22:34:44","guid":{"rendered":"https:\/\/database.guide\/?p=24367"},"modified":"2022-03-16T08:34:44","modified_gmt":"2022-03-15T22:34:44","slug":"sqlite-substring-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/sqlite-substring-explained\/","title":{"rendered":"SQLite SUBSTRING() Explained"},"content":{"rendered":"\n<p>In SQLite, <code>substring()<\/code> is an alias for <code>substr()<\/code>. <\/p>\n\n\n\n<p>It returns a substring from a string, based on a given starting location within the string. Two arguments are required, and a third optional argument is accepted.<\/p>\n\n\n\n<p>The <code>substring()<\/code> naming was introduced in SQLite 3.34.0, which was released on 1st December 2020. The reason that the <code>substring()<\/code> syntax was introduced was for compatibility with SQL Server.<\/p>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\">Syntax<\/h2>\n\n\n\n<p>So you can now call the function in any of the following ways: <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>substr(X,Y,Z)\nsubstr(X,Y)\nsubstring(X,Y,Z)\nsubstring(X,Y)<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li><code>X<\/code> is the full string that contains the substring you want to return.<\/li><li><code>Y<\/code> is the location of the first character of substring that you want to return from that string.<\/li><li><code>Z<\/code> is the number of characters that you want returned. If omitted, all subsequent characters are returned (starting from <code>Y<\/code>).<\/li><\/ul>\n\n\n\n<p>If <code>X<\/code> is a string then characters indices refer to actual UTF-8 characters. If <code>X<\/code> is a BLOB then the indices refer to bytes.<\/p>\n\n\n\n<p>As mentioned, the <code>substring()<\/code> syntax is only available from SQLite 3.34.0.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Example with 3 Arguments<\/h2>\n\n\n\n<p>Here&#8217;s a basic example to demonstrate how to use <code>substring()<\/code> with three arguments.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT substring('Dolemite', 3, 4);<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">lemi<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Example with 2 Arguments<\/h2>\n\n\n\n<p>If I omit the third argument from the previous example I get the following result.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT substring('Dolemite', 3);<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">lemite<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Negative Starting Point<\/h2>\n\n\n\n<p>You can provide a negative value for the second argument. When you do this, the first character of the substring is found by counting from the right rather than the left.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT substring('Dolemite', -3);<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">ite<\/pre>\n\n\n\n<p>Here&#8217;s another example, this time I specify the length of the substring.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT substring('Dolemite', -7, 4);<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">olem<\/pre>\n\n\n\n<p>So negative values have the same effect as when using the <a href=\"https:\/\/database.guide\/how-to-use-substring-in-mysql\/\" class=\"aioseop-link\"><code>substring()<\/code> function in MySQL<\/a>. MySQL also has a <code>substr()<\/code> function, which is a synonym of its <code>substring()<\/code> function. <\/p>\n\n\n\n<p>However, this is different to how the <a href=\"https:\/\/database.guide\/what-is-sql-server\/\" class=\"aioseop-link\">SQL Server<\/a> treats negative values. When you pass a negative value to <a href=\"https:\/\/database.guide\/how-to-return-a-substring-from-a-string-in-sql-server-using-substring-function\/\" class=\"aioseop-link\"><code>substring()<\/code> in SQL Server<\/a>, it will simply start counting at an imaginary point before the string begins. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Negative Substring Length<\/h2>\n\n\n\n<p>The previous example used a negative starting point. In this example, I&#8217;m going to use a negative length. By this I mean I&#8217;m going to provide a negative value for the third argument.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT substring('Dolemite', -1, -4);<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">emit<\/pre>\n\n\n\n<p>So providing a negative value for the third argument results in the characters preceding the starting point being returned.<\/p>\n\n\n\n<p>This also applies when the second argument is a positive value.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT substring('Dolemite', 6, -4);<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">olem<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Database Example<\/h2>\n\n\n\n<p>Here&#8217;s an example that uses <code>substring()<\/code> in a database query against the <a class=\"aioseop-link\" href=\"https:\/\/database.guide\/2-sample-databases-sqlite\/\">Chinook sample database<\/a>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n    substring(Name, 1, 10),\n    Name\nFROM Artist\nORDER BY Name DESC\nLIMIT 10;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">substring(Name, 1, 10)  Name                             \n----------------------  ---------------------------------\nZeca Pagod              Zeca Pagodinho                   \nYoussou N'              Youssou N'Dour                   \nYo-Yo Ma                Yo-Yo Ma                         \nYehudi Men              Yehudi Menuhin                   \nXis                     Xis                              \nWilhelm Ke              Wilhelm Kempff                   \nWhitesnake              Whitesnake                       \nVin\u00edcius E              Vin\u00edcius E Qurteto Em Cy         \nVin\u00edcius E              Vin\u00edcius E Odette Lara           \nVin\u00edcius D              Vin\u00edcius De Moraes &amp; Baden Powell<\/pre>\n\n\n\n<p>In this case, I returned the first ten characters from the <code>Name<\/code> column. I also returned the full contents of the <code>Name<\/code> column in order to compare the results.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In SQLite, substring() is an alias for substr(). It returns a substring from a string, based on a given starting location within the string. Two arguments are required, and a third optional argument is accepted. The substring() naming was introduced in SQLite 3.34.0, which was released on 1st December 2020. The reason that the substring() &#8230; <a title=\"SQLite SUBSTRING() Explained\" class=\"read-more\" href=\"https:\/\/database.guide\/sqlite-substring-explained\/\" aria-label=\"Read more about SQLite SUBSTRING() Explained\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[100],"tags":[93,67,20],"class_list":["post-24367","post","type-post","status-publish","format-standard","hentry","category-sqlite","tag-functions","tag-string-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/24367","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=24367"}],"version-history":[{"count":2,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/24367\/revisions"}],"predecessor-version":[{"id":24369,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/24367\/revisions\/24369"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=24367"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=24367"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=24367"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}