{"id":16414,"date":"2021-04-20T10:09:06","date_gmt":"2021-04-20T00:09:06","guid":{"rendered":"https:\/\/database.guide\/?p=16414"},"modified":"2021-04-20T10:09:06","modified_gmt":"2021-04-20T00:09:06","slug":"mariadb-substr-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-substr-explained\/","title":{"rendered":"MariaDB SUBSTR() Explained"},"content":{"rendered":"\n

In MariaDB, SUBSTR()<\/code> is a synonym for SUBSTRING()<\/a><\/code>. <\/p>\n\n\n\n

It’s a built-in string function that returns a substring from a given string.<\/p>\n\n\n\n

It requires at least two arguments; the string, and the position for which to extract the substring from. It also accepts an optional third argument that allows you to specify how long the substring should be.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax takes the following forms:<\/p>\n\n\n\n

SUBSTR(str,pos), \nSUBSTR(str FROM pos), \nSUBSTR(str,pos,len),\nSUBSTR(str FROM pos FOR len)<\/code><\/pre>\n\n\n\n
Where str<\/code> is the string, pos<\/code> is the starting position of the substring, and len<\/code> is the number of characters to extract.<\/p>\n\n\n\n
The two forms that use the FROM<\/code> keyword are standard SQL syntax.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s a basic example:<\/p>\n\n\n\n
SELECT SUBSTR('Good doggy', 6);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------------------+\n| SUBSTR('Good doggy', 6) |\n+-------------------------+\n| doggy                   |\n+-------------------------+<\/pre>\n\n\n\n
Here’s the same example, but using the standard SQL syntax:<\/p>\n\n\n\n
SELECT SUBSTR('Good doggy' FROM 6);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------------------+\n| SUBSTR('Good doggy' FROM 6) |\n+-----------------------------+\n| doggy                       |\n+-----------------------------+<\/pre>\n\n\n\n
Substring Length<\/h2>\n\n\n\n
Here’s an example that specifies the length of the substring to extract:<\/p>\n\n\n\n
SELECT SUBSTR('Good doggy', 6, 3);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----------------------------+\n| SUBSTR('Good doggy', 6, 3) |\n+----------------------------+\n| dog                        |\n+----------------------------+<\/pre>\n\n\n\n
And here it is using standard SQL syntax:<\/p>\n\n\n\n
SELECT SUBSTR('Good doggy' FROM 6 FOR 3);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------------------------+\n| SUBSTR('Good doggy' FROM 6 FOR 3) |\n+-----------------------------------+\n| dog                               |\n+-----------------------------------+<\/pre>\n\n\n\n
Negative Position<\/h2>\n\n\n\n
Specifying a negative value for the position causes the starting position to be counted backwards from the end of the string:<\/p>\n\n\n\n
SELECT SUBSTR('Good doggy', -5);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------+\n| SUBSTR('Good doggy', -5) |\n+--------------------------+\n| doggy                    |\n+--------------------------+<\/pre>\n\n\n\n
A negative position can also be used when using the standard SQL syntax:<\/p>\n\n\n\n
SELECT SUBSTR('Good doggy' FROM -5 FOR 3);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------------------+\n| SUBSTR('Good doggy' FROM -5 FOR 3) |\n+------------------------------------+\n| dog                                |\n+------------------------------------+<\/pre>\n\n\n\n
In this case I also set a length for the substring.<\/p>\n\n\n\n
Oracle Mode<\/h2>\n\n\n\n
When in Oracle mode, a start position of 0<\/code> (zero) is treated as 1<\/code>. However, a start position of 1<\/code> is also treated as 1<\/code>.<\/p>\n\n\n\n
This is in contrast to other modes, where 0<\/code> will return an empty string.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
SET SQL_MODE=ORACLE;\nSELECT \n    SUBSTR('Good doggy', 0) AS \"0\",\n    SUBSTR('Good doggy', 1) AS \"1\";<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------+------------+\n| 0          | 1          |\n+------------+------------+\n| Good doggy | Good doggy |\n+------------+------------+<\/pre>\n\n\n\n
Here it is in default mode:<\/p>\n\n\n\n
SET SQL_MODE=DEFAULT;\nSELECT \n    SUBSTR('Good doggy', 0) AS \"0\",\n    SUBSTR('Good doggy', 1) AS \"1\";<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------+------------+\n| 0    | 1          |\n+------+------------+\n|      | Good doggy |\n+------+------------+<\/pre>\n\n\n\n
Null Arguments<\/h2>\n\n\n\n
If any (or all) of the arguments are null<\/code>, the SUBSTR()<\/code> function returns null<\/code>:<\/p>\n\n\n\n
SELECT \n    SUBSTR(null, 3, 3),\n    SUBSTR('Doggy', null, 3),\n    SUBSTR('Doggy', 3, null),\n    SUBSTR(null, null, null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------+--------------------------+--------------------------+--------------------------+\n| SUBSTR(null, 3, 3) | SUBSTR('Doggy', null, 3) | SUBSTR('Doggy', 3, null) | SUBSTR(null, null, null) |\n+--------------------+--------------------------+--------------------------+--------------------------+\n| NULL               | NULL                     | NULL                     | NULL                     |\n+--------------------+--------------------------+--------------------------+--------------------------+<\/pre>\n\n\n\n
Missing Arguments<\/h2>\n\n\n\n
Calling SUBSTR()<\/code> without passing any arguments results in an error:<\/p>\n\n\n\n
SELECT SUBSTR();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1<\/pre>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, SUBSTR() is a synonym for SUBSTRING(). It’s a built-in string function that returns a substring from a given string. It requires at least two arguments; the string, and the position for which to extract the substring from. It also accepts an optional third argument that allows you to specify how long the substring … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[117],"tags":[93,67,20],"class_list":["post-16414","post","type-post","status-publish","format-standard","hentry","category-mariadb","tag-functions","tag-string-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/16414","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=16414"}],"version-history":[{"count":4,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/16414\/revisions"}],"predecessor-version":[{"id":16419,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/16414\/revisions\/16419"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=16414"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=16414"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=16414"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}