{"id":36314,"date":"2024-06-17T11:13:25","date_gmt":"2024-06-17T01:13:25","guid":{"rendered":"https:\/\/database.guide\/?p=36314"},"modified":"2024-06-25T11:50:09","modified_gmt":"2024-06-25T01:50:09","slug":"a-quick-look-at-the-overlay-function-in-postgresql","status":"publish","type":"post","link":"https:\/\/database.guide\/a-quick-look-at-the-overlay-function-in-postgresql\/","title":{"rendered":"A Quick Look at the OVERLAY() Function in PostgreSQL"},"content":{"rendered":"\n

In PostgreSQL, we can use the overlay()<\/code> function to replace a substring within a string. The function allows us to be very specific with regards to where in the string to start the operation and how much of it should be replaced.<\/p>\n\n\n\n\n\n\n\n

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

The syntax goes like this:<\/p>\n\n\n\n

overlay ( string text PLACING newsubstring text FROM start integer [ FOR count integer ] ) <\/code><\/pre>\n\n\n\n
The following examples demonstrate the various arguments.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s a basic example:<\/p>\n\n\n\n
SELECT overlay('Cashflow' PLACING 'ino' FROM 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Casinoow<\/pre>\n\n\n\n
Here, we replaced part of the string Cashflow<\/code> with the substring ino<\/code>, starting from the fourth character.<\/p>\n\n\n\n
If the FROM<\/code> argument is greater than the length of the string, the substring is appended to the string:<\/p>\n\n\n\n
SELECT overlay('Cashflow' PLACING 'ino' FROM 20);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Cashflowino<\/pre>\n\n\n\n
The FOR<\/code> Argument<\/h2>\n\n\n\n
We have the option of using the FOR<\/code> argument to specify how long to extend the replacement within the string. By default, this is the length of the new substring, but we can change this with the FOR<\/code> argument.<\/p>\n\n\n\n
We could therefore modify our first example like this:<\/p>\n\n\n\n
SELECT overlay('Cashflow' PLACING 'ino' FROM 4 FOR 5);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Casino<\/pre>\n\n\n\n
To further demonstrate how this argument works, the following example shows how different values affect the outcome:<\/p>\n\n\n\n
\\x\nSELECT \n    overlay('Cashflow' PLACING 'ino' FROM 4 FOR -4) AS \"-4\",\n    overlay('Cashflow' PLACING 'ino' FROM 4 FOR -3) AS \"-3\",\n    overlay('Cashflow' PLACING 'ino' FROM 4 FOR -2) AS \"-2\",\n    overlay('Cashflow' PLACING 'ino' FROM 4 FOR -1) AS \"-1\",\n    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 0) AS \"0\",\n    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 1) AS \"1\",\n    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 2) AS \"2\",\n    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 3) AS \"3\",\n    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 4) AS \"4\",\n    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 5) AS \"5\",\n    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 6) AS \"6\";<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
-4 | CasinoCashflow
-3 | CasinoCashflow
-2 | Casinoashflow
-1 | Casinoshflow
0  | Casinohflow
1  | Casinoflow
2  | Casinolow
3  | Casinoow
4  | Casinow
5  | Casino
6  | Casino<\/pre>\n\n\n\n
Using a Negative FROM<\/code> Argument<\/h2>\n\n\n\n
We can see from the previous example that we can use a negative value for the FOR<\/code> argument. But if we use a negative value for the FROM<\/code> argument, it’s a different story:<\/p>\n\n\n\n
SELECT overlay('Cashflow' PLACING 'ino' FROM -4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR:  negative substring length not allowed<\/pre>\n\n\n\n
We get the same error when using zero:<\/p>\n\n\n\n
SELECT overlay('Cashflow' PLACING 'ino' FROM 0);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR:  negative substring length not allowed<\/pre>\n\n\n\n
When the New String is Empty<\/h2>\n\n\n\n
If the replacement string is empty, then the original string is returned unchanged:<\/p>\n\n\n\n
SELECT overlay('Cashflow' PLACING '' FROM 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Cashflow<\/pre>\n\n\n\n
When the Original String is Empty<\/h2>\n\n\n\n
If the original string is empty, then the new string is returned:<\/p>\n\n\n\n
SELECT overlay('' PLACING 'ino' FROM 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ino<\/pre>\n\n\n\n
When Both Original String and New String are Empty<\/h2>\n\n\n\n
Unsurprisingly, if both the new string and the original string are empty, then an empty string is returned:<\/p>\n\n\n\n
SELECT overlay('' PLACING '' FROM 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
 overlay 
---------
 <\/pre>\n","protected":false},"excerpt":{"rendered":"
In PostgreSQL, we can use the overlay() function to replace a substring within a string. The function allows us to be very specific with regards to where in the string to start the operation and how much of it should be replaced.<\/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-36314","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\/36314","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=36314"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36314\/revisions"}],"predecessor-version":[{"id":36605,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36314\/revisions\/36605"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=36314"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=36314"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=36314"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}