{"id":36334,"date":"2024-06-22T11:13:24","date_gmt":"2024-06-22T01:13:24","guid":{"rendered":"https:\/\/database.guide\/?p=36334"},"modified":"2024-06-22T11:13:26","modified_gmt":"2024-06-22T01:13:26","slug":"how-the-substring-function-works-in-postgresql","status":"publish","type":"post","link":"https:\/\/database.guide\/how-the-substring-function-works-in-postgresql\/","title":{"rendered":"How the SUBSTRING() Function Works in PostgreSQL"},"content":{"rendered":"\n

In PostgreSQL, we can use the substring()<\/code> function to extract the first substring matching the specified POSIX or SQL regular expression.<\/p>\n\n\n\n\n\n\n\n

POSIX Regular Expressions<\/h2>\n\n\n\n

When used with POSIX regular expressions, the syntax of substring()<\/code> goes like this:<\/p>\n\n\n\n

substring ( string text FROM pattern text )<\/code><\/pre>\n\n\n\n
Here’s an example that uses a POSIX regular expression:<\/p>\n\n\n\n
SELECT substring('Restaurant' FROM 'R..t');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Rest<\/pre>\n\n\n\n
In POSIX, the dot (.<\/code>) matches any character. We had two dots which matched e<\/code> and s<\/code>, and so the function returned the substring Rest<\/code> because that’s the first instance in our string that matches this particular POSIX regular expression.<\/p>\n\n\n\n
Here are some more POSIX examples:<\/p>\n\n\n\n
\\x\nSELECT \n    substring('Restaurant' FROM 'Rest') AS \"Rest\",\n    substring('Restaurant' FROM 'R.t') AS \"R.t\",\n    substring('Restaurant' FROM 'R*t') AS \"R*t\",\n    substring('Restaurant' FROM 'r..t') AS \"r..t\",\n    substring('Restaurant' FROM '^R') AS \"^R\",\n    substring('Restaurant' FROM '^r') AS \"^r\",\n    substring('Restaurant' FROM 'beer|sta') AS \"beer|sta\";<\/code><\/pre>\n\n\n\n
Result (using vertical output):<\/p>\n\n\n\n
Rest     | Rest
R.t      | null
R*t      | t
r..t     | rant
^R       | R
^r       | null
beer|sta | sta<\/pre>\n\n\n\n
SQL Regular Expressions<\/h2>\n\n\n\n
When used with SQL regular expressions, the syntax goes like this:<\/p>\n\n\n\n
substring ( string text SIMILAR pattern text ESCAPE escape text ) <\/code><\/pre>\n\n\n\n
The above form was specified in SQL:2003. <\/p>\n\n\n\n
The following form was specified in SQL:1999 and is considered obsolete:<\/p>\n\n\n\n
substring ( string text FROM pattern text FOR escape text )<\/code><\/pre>\n\n\n\n
So we should focus only on the first syntax.<\/p>\n\n\n\n
Here’s an example that uses a SQL regular expression:<\/p>\n\n\n\n
SELECT substring('Restaurant' SIMILAR '%#\"R__t#\"%' ESCAPE '#');<\/code><\/pre>\n\n\n\n
Result (using vertical output):<\/p>\n\n\n\n
Rest<\/pre>\n\n\n\n
To indicate the part of the pattern for which the matching data sub-string is of interest, the pattern should contain two occurrences of the escape character followed by a double quote (\"<\/code>). The text matching the portion of the pattern between these separators is returned when the match is successful.<\/p>\n\n\n\n
Here are some more examples:<\/p>\n\n\n\n
SELECT \n    substring('Restaurant' SIMILAR 'R%t' ESCAPE '#') AS \"R%t\",\n    substring('Restaurant' SIMILAR 's%a' ESCAPE '#') AS \"s%a\",\n    substring('Restaurant' SIMILAR '%#\"s_a#\"%' ESCAPE '#') AS \"%#\"\"s_a#\"\"%\",\n    substring('Restaurant' SIMILAR '%#\"s%a#\"%' ESCAPE '#') AS \"%#\"\"s%a#\"\"%\",\n    substring('Restaurant' SIMILAR '%(sta|xyz)%' ESCAPE '#') AS \"%(sta|xyz)%\",\n    substring('Restaurant' SIMILAR '%#\"(sta|xyz)#\"%' ESCAPE '#') AS \"%#\"\"(sta|xyz)#\"\"%\";<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
R%t             | Restaurant
s%a             | null
%#\"s_a#\"%       | sta
%#\"s%a#\"%       | staura
%(sta|xyz)%     | Restaurant
%#\"(sta|xyz)#\"% | sta<\/pre>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
In PostgreSQL, we can use the substring() function to extract the first substring matching the specified POSIX or SQL regular expression.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[93,67,20],"class_list":["post-36334","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-functions","tag-string-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36334","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=36334"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36334\/revisions"}],"predecessor-version":[{"id":36535,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36334\/revisions\/36535"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=36334"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=36334"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=36334"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}