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.

Example

Here’s an example to demonstrate:

SELECT starts_with('PostgreSQL', 'Post');

Result:

t

In this case t for true was returned, which means that the string does in fact start with the substring.

And here’s a false result:

SELECT starts_with('PostgreSQL', 'post');

Result:

f

It returned f for false because the substring started with a lowercase p, whereas the string starts with uppercase P.

The following example uses the lower() function to convert the string to lowercase:

SELECT starts_with(lower('PostgreSQL'), 'post');

Result:

t

This time it’s a match.

Null Arguments

If any of the arguments are null, null is returned:

SELECT 
    starts_with('PostgreSQL', null) AS "1",
    starts_with(null, 'Post') AS "2",
    starts_with(null, null) AS "3";

Result:

  1   |  2   |  3   
------+------+------
 null | null | null

Wrong Argument Type

The starts_with() function only accepts text. We’ll get an error if we pass the wrong type:

SELECT starts_with('PostgreSQL', 123);

Result:

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.

And:

SELECT starts_with( 123, 'Post' );

Result:

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.

However, we can pass numbers as text:

SELECT starts_with( '1234567', '123' );

Result:

t