{"id":36664,"date":"2024-06-27T11:09:13","date_gmt":"2024-06-27T01:09:13","guid":{"rendered":"https:\/\/database.guide\/?p=36664"},"modified":"2024-06-27T11:09:14","modified_gmt":"2024-06-27T01:09:14","slug":"how-strpos-works-in-postgresql","status":"publish","type":"post","link":"https:\/\/database.guide\/how-strpos-works-in-postgresql\/","title":{"rendered":"How STRPOS() Works in PostgreSQL"},"content":{"rendered":"\n

In PostgreSQL, the strpos()<\/code> function returns the first starting position of a given substring within a string.<\/p>\n\n\n\n

We pass the string as the first argument and the substring as the second.<\/p>\n\n\n\n\n\n\n\n

Example<\/h2>\n\n\n\n

Here’s an example to demonstrate:<\/p>\n\n\n\n

SELECT strpos('PostgreSQL', 'SQL');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
8<\/pre>\n\n\n\n
It’s the same result we’d get if we were to use the position()<\/code> function<\/a>:<\/p>\n\n\n\n
SELECT position('SQL' in 'PostgreSQL');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
8<\/pre>\n\n\n\n
No Match<\/h2>\n\n\n\n
If the substring doesn’t exist in the string, then the function returns zero:<\/p>\n\n\n\n
SELECT strpos('PostgreSQL', 'sql');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
0<\/pre>\n\n\n\n
In this case I used the same substring, except this time it was in lowercase. This resulted in 0<\/code> being returned due to no match.<\/p>\n\n\n\n
Null Arguments<\/h2>\n\n\n\n
If any of the arguments is null<\/code>, then null<\/code> is returned:<\/p>\n\n\n\n
SELECT \n    strpos('PostgreSQL', null) AS \"1\",\n    strpos(null, 'SQL') AS \"2\",\n    strpos(null, null) AS \"3\";<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
  1   |  2   |  3   
------+------+------
 null | null | null<\/pre>\n\n\n\n
Wrong Argument Type<\/h2>\n\n\n\n
Both arguments need to be text. Passing the wrong type results in an error:<\/p>\n\n\n\n
SELECT strpos('PostgreSQL', 3);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR:  function strpos(unknown, integer) does not exist
LINE 1: SELECT strpos('PostgreSQL', 3);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.<\/pre>\n\n\n\n
And:<\/p>\n\n\n\n
SELECT strpos(123, 'SQL');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR:  function strpos(integer, unknown) does not exist
LINE 1: SELECT strpos(123, 'SQL');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.<\/pre>\n\n\n\n
But of course, we can provide the text representation of such values:<\/p>\n\n\n\n
SELECT strpos('1234567', '56');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
5<\/pre>\n","protected":false},"excerpt":{"rendered":"
In PostgreSQL, the strpos() function returns the first starting position of a given substring within a string. We pass the string as the first argument and the substring as the second.<\/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-36664","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\/36664","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=36664"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36664\/revisions"}],"predecessor-version":[{"id":36672,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36664\/revisions\/36672"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=36664"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=36664"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=36664"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}