Odd bug in SQL TRIM() function

Question

I have the following table:

select * from top3art;

            path             | count
-----------------------------+--------
 /article/candidate-is-jerk  | 338647
 /article/bears-love-berries | 253801
 /article/bad-things-gone    | 170098

I want to trim off '/article/' in path values, so I do this:

select *, trim(leading '/article/' from path) from top3art;

            path             | count  |       ltrim
-----------------------------+--------+--------------------
 /article/candidate-is-jerk  | 338647 | ndidate-is-jerk
 /article/bears-love-berries | 253801 | bears-love-berries
 /article/bad-things-gone    | 170098 | bad-things-gone

Rows 2 and 3 work just fine. But what happened to the 1st row?? It trimmed '/article/ca'. Why did it take 2 more characters?

Now watch what happens when I just trim '/articl':

select *, trim(leading '/articl' from path) as test from top3art;

            path             | count  |         test
-----------------------------+--------+----------------------
 /article/candidate-is-jerk  | 338647 | e/candidate-is-jerk
 /article/bears-love-berries | 253801 | e/bears-love-berries
 /article/bad-things-gone    | 170098 | e/bad-things-gone

That works as expected... Now watch what happens when I add one more char in my trim clause, '/article':

select *, trim(leading '/article' from path) as test from top3art;

            path             | count  |        test
-----------------------------+--------+--------------------
/article/candidate-is-jerk  | 338647 | ndidate-is-jerk
/article/bears-love-berries | 253801 | bears-love-berries
/article/bad-things-gone    | 170098 | bad-things-gone

Same as the first result! I can't make sense of this. Why is this happening? How do I fix it?

Answer 1

trim removes any character in the first argument from the second argument, so it also removes the c and the a of "candidate". Instead of trim, you could use a split_part call:

select *, split_part(path, '/article/', 2) as test from top3art;