Home using length function in REGEXP_REPLACE() in Postgres
Reply: 2

using length function in REGEXP_REPLACE() in Postgres

slider_rahul
1#
slider_rahul Published in 2018-01-12 12:05:44Z

I am removing that last 3 characters from the string "ABC123" using regexp_replace function in Oracle using the below statement

select REGEXP_REPLACE('ABC123','123','', LENGTH('ABC123') - 3) from dual;

The same result can be achieved in Postgres with the below statements,

select regexp_replace('ABC123','[123]', '','g')
select translate('ABC123','123', '');

Is there any way I can use the length function for replace as I have used in Oracle?

Gordon Linoff
2#
Gordon Linoff Reply to 2018-01-12 12:15:05Z

Why not simply use left()?

select left('ABC123', length('ABC123') - 3)

The same idea can be used in Oracle as well, but you need to use the substr() function. This should be more efficient in both databases.

WillyB
3#
WillyB Reply to 2018-01-12 17:49:14Z

You could also look into the trim functionality. http://www.postgresqltutorial.com/postgresql-trim-function/

"select REGEXP_REPLACE('ABC123','123','', LENGTH('ABC123') - 3) from dual;" would become select ltrim('ABC123','ABC') from dual; resulting in 123

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.297628 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO