Escape underscore '_' in PostgreSQL trim() function?

user_8639615 0
Viewed 910

How to escape underscore '_' in PostgreSQL trim() function?

I try to remove 'vt_' from begining of text (for example 'vt_test' ) using trim() function:

select trim(leading 'vt_' from 'vt_test');
select ltrim('vt_test', 'vt_');
select ltrim('vt_test', 'vt\_');
select ltrim('vt_test', 'vt\\_');

Returns:

est

But I would like to get:

test

I can do that using replace() but I would like to know why trim() doesn't work.

Tested on Postgres 12 and 11.

SUMMARY

  1. Function trim removes all leading instances of the listed characters - the order of the characters doesn't matter. You get the same result using trim(leading '_tv' from 'vt_test').
  2. I think, that the best solution is to use select regexp_replace('vt_test', '^vt_', '') because I only want to remove this leading string only if vt_ exists at the begining (I'm sorry, but I didn't mention it before).

Thanks a_horse_with_no_name, Mureinik and Erwin Brandstetter for help!

trim postgresql sql replace
2 Answers

trim() works as expected: It removes all leading instances of the listed characters. The manual:

trim([leading | trailing | both] [characters] from string)

Remove the longest string containing only characters from characters (a space by default) from the start, end, or both ends (both is the default) of string

So vt_t is removed, not just vt_.

The issue is unrelated to the underscore _, which has no special meaning in this context.

The fastest alternative for the particular task:

SELECT right('vt_test', -3);
user_9398600

The issue here isn't escaping, ltrim is just the wrong tool for the job. According to the documentation, this function will

Remove the longest string containing only characters from characters (a space by default) from the start of string.

You could use regexp_replace to get the desired effect:

select regexp_replace('vt_test', '^vt_', '')
user_24227760
Related