CODEWITHSUNDEEP
postgresqlviewtrim
sam-la
sam-la

Reputation: 21

How to use TRIM on a VIEW

I have a VIEW like this:

SELECT * FROM test --will show:
          path        
------------------------
 /downloads/abc-dbc-abcd
 /downloads/dfg-gfd-hjkl
 /downloads/tyu-iti-titk

How do I use TRIM to only select the trailing part of the strings in column path?

In PostgreSQL, I've tried:

SELECT TRIM('/downloads/' FROM (SELECT * FROM test);
SELECT TRIM('/downloads/' FROM (SELECT path FROM test);

I expect to receive the output strings as just 'abc-dbc-abcd', etc.; the same as input but with '/downloads/' removed. I have been getting an error...

ERROR: more than one row returned by a subquery used as an expression

Upvotes: 0

Views: 446

Answers (2)

dwir182
dwir182

Reputation: 1549

Your error are because you are using SubQuery in your TRIM() function and it returned more than 1 row so the error show.

And I prefer you use REPLACE() rather than TRIM() function here. From Documentation REPLACE :

Replace all occurrences in string of substring from with substring to

For the query :

SELECT REPLACE(path, '/downloads/', '') from test;

You can see here for Demo

Upvotes: 3

Mani Kandan
Mani Kandan

Reputation: 107

Try this.

SELECT LTRIM(RTRIM(REPLACE(path,'/downloads/','')))

Upvotes: 1

Related Questions