How do I convert a T-SQL query to PostgreSQL that concatenates substrings?

Question

This is my query:

SELECT
    *,
    CONCAT(
        RIGHT( account_no, 4),
        RIGHT( customer_id, 5 )
    ) AS "password for my diplomo"
FROM
    account_info;

But I get this error:

Error: function left(bigint, integer) does not exist;

My table is:

CREATE TABLE account_info (
    account_no  bigint       NOT NULL PRIMARY KEY,
    customer_id varchar(...)
)

The query you have shown can not generate the error "left() does not exist" as your query does not use a left() function — user330315
– user330315, Commented Nov 25, 2021 at 17:55

Stefanov.sm · Accepted Answer · 2021-11-25 21:42:37Z

0

Postgres functions left and right expect their first argument be text. So first cast account_no to type text and your query (a bit simplified) will work.

SELECT *,
       right(account_no::text, 4) || right(customer_id, 5) as pfmd
FROM account_info;

Unrelated but the best practice under Postgres is to use type text instead of char or varchar.

edited Nov 25, 2021 at 21:42

answered Nov 25, 2021 at 21:36

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pandibabu Over a year ago

Hey, Thank you for the answer. :) This one works well

Pandibabu Over a year ago

I have one more followup question, if I want to take middle three numbers from column "account_no" instead of last 4 how do I do that? Like from 3rd digit of account number to 6th digit of ACC no. totally 4 digits?

Stefanov.sm Over a year ago

Use substring function. substring(account_no::text from 3 for 4)

Dai · Accepted Answer · 2021-11-25 18:07:20Z

0

You seem to be using a reference for T-SQL or JET Red SQL (for MS SQL Server and MS Access respectively) when you're actually using PostgreSQL which uses completely different functions (and syntax) for string/text processing.

This is the PostgreSQL v12 manual page for string functions and other syntax. You should read it.

As for making your query run on PostgreSQL, change it to this:

Convert account_no to a varchar type so you can use SUBSTRING with it.
- I think it might work without it, but I don't like relying on implicit conversion, especially when localization/locale/culture issues might pop-up.

The LEFT and RIGHT functions for extracting substrings can be reimplemented like so:

LEFT( text, length ) == SUBSTRING( text FROM 0 FOR length )
RIGHT( text, length ) == SUBSTRING( text FROM CHAR_LENGTH( text ) - length )

And use || to concatenate text values together.

Like so:

SELECT
    q.*,
    (
        SUBSTRING( q.account_no_text FROM CHAR_LENGTH( q.account_no_text ) - 4 )
        ||
        SUBSTRING( q.customer_id FROM CHAR_LENGTH( q.customer_id ) - 5 )
    ) AS "password for my diplomo"
FROM
    (
        SELECT
            ai.*,
            ai.account_no::varchar(10) AS account_no_text
        FROM
            account_info AS ai
    )
        AS q

Here is a runnable DB-Fiddle.

Screenshot proof:

edited Nov 25, 2021 at 18:07

answered Nov 25, 2021 at 17:57

Dai

157k31 gold badges314 silver badges435 bronze badges

1 Comment

Pandibabu Over a year ago

Thank you so much for the answer. :) Happy Thanksgiving.

Collectives™ on Stack Overflow

How do I convert a T-SQL query to PostgreSQL that concatenates substrings?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related