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
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').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() 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 (bothis the default) ofstring
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);
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_', '')