CODEWITHSUNDEEP
postgresqlsubquery
Tobias Hagenbeek
Tobias Hagenbeek

Reputation: 1213

postgres, substring a subselect

I have a query that uses a subselect like this

SELECT "columnA","columnB", (SELECT column1 FROM tableB WHERE id=1 LIMIT 1) as text
FROM tableA WHERE id=1

Now i would like to only get the last 3 chars from my "as text" column. I have tried to apply the substring or right around my subselect but that returns an error, can anyone explain why and how to do this properly?

Upvotes: 0

Views: 611

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17137

You need to use internal function substring matching POSIX regular expression

SELECT "columnA","columnB", (SELECT substring(column1::TEXT from '...$') FROM tableB WHERE id=1 LIMIT 1) as text
FROM tableA WHERE id=1

Please keep in mind that this way, if you have more than 1 record in tableA that matches your WHERE criteria, you will still be getting the same value in variable text for this query.

Upvotes: 1

Related Questions