{"id":36656,"date":"2024-07-15T11:51:37","date_gmt":"2024-07-15T01:51:37","guid":{"rendered":"https:\/\/database.guide\/?p=36656"},"modified":"2024-07-15T11:51:39","modified_gmt":"2024-07-15T01:51:39","slug":"a-quick-look-at-postgresqls-starts_with-function","status":"publish","type":"post","link":"https:\/\/database.guide\/a-quick-look-at-postgresqls-starts_with-function\/","title":{"rendered":"A Quick Look at PostgreSQL’s STARTS_WITH() Function"},"content":{"rendered":"\n

When using PostgreSQL, we can use the starts_with()<\/code> function to check whether or not a string starts with a given substring.<\/p>\n\n\n\n

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

It returns true if the string does start with the substring, otherwise it returns false.<\/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 starts_with('PostgreSQL', 'Post');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
t<\/pre>\n\n\n\n
In this case t<\/code> for true was returned, which means that the string does in fact start with the substring.<\/p>\n\n\n\n
And here’s a false result:<\/p>\n\n\n\n
SELECT starts_with('PostgreSQL', 'post');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
f<\/pre>\n\n\n\n
It returned f<\/code> for false because the substring started with a lowercase p<\/code>, whereas the string starts with uppercase P<\/code>.<\/p>\n\n\n\n
The following example uses the lower()<\/code> function<\/a> to convert the string to lowercase:<\/p>\n\n\n\n
SELECT starts_with(lower('PostgreSQL'), 'post');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
t<\/pre>\n\n\n\n
This time it’s a match.<\/p>\n\n\n\n
Null Arguments<\/h2>\n\n\n\n
If any of the arguments are null, null<\/code> is returned:<\/p>\n\n\n\n
SELECT \n    starts_with('PostgreSQL', null) AS \"1\",\n    starts_with(null, 'Post') AS \"2\",\n    starts_with(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
The starts_with()<\/code> function only accepts text. We’ll get an error if we pass the wrong type:<\/p>\n\n\n\n
SELECT starts_with('PostgreSQL', 123);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR:  function starts_with(unknown, integer) does not exist
LINE 1: SELECT starts_with('PostgreSQL', 123);
               ^
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 starts_with( 123, 'Post' );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR:  function starts_with(integer, unknown) does not exist
LINE 1: SELECT starts_with( 123, 'Post' );
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.<\/pre>\n\n\n\n
However, we can pass numbers as text:<\/p>\n\n\n\n
SELECT starts_with( '1234567', '123' );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
t<\/pre>\n","protected":false},"excerpt":{"rendered":"
When using PostgreSQL, we can use the starts_with() function to check whether or not a string starts with a given substring. We pass the string as the first argument, and the substring as the second. It returns true if the string does start with the substring, otherwise it returns false.<\/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-36656","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\/36656","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=36656"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36656\/revisions"}],"predecessor-version":[{"id":36663,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36656\/revisions\/36663"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=36656"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=36656"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=36656"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}