CODEWITHSUNDEEP
postgresql
Big Charlie
Big Charlie

Reputation: 79

The RIGHT() function in PostgreSQL gives an error

I'm trying to use the RIGHT() function it so it will only display the last 4 digits of a credit card number pulled from the customer table. This is what I have so far:

create function get_customer(text) returns setof cusinfo as

$$

select upper(first_name)||' '||upper(last_name) as full_name, upper(address), upper(city)||', '||upper(state)||' '||zip as citystatezip, email, '************'||right(cc_number,4), cc_name

from customer

where customer_id = $1;

$$ language sql;

The error I am being given is:

psql:finalproject.sql:273: ERROR: function right(text, integer) does not exist LINE 3: ...|' '||zip as citystatezip, email, '****'||right(cc_n...

Any ideas as to why this is happening? I tried only using RIGHT() by itself and putting in something like RIGHT('Help me', 2), but I get the same error.

Upvotes: 6

Views: 10873

Answers (3)

Ken Benson
Ken Benson

Reputation: 352

RIGHT(str, num) - was added to Postgresql in version 9.1

Upvotes: 4

Max Heiber
Max Heiber

Reputation: 15502

You can create the missing RIGHT function:

DROP FUNCTION IF EXISTS
    RIGHT
        (
            IN_STRING VARCHAR(4000),
            RIGHT_OFFSET INT
        );

CREATE FUNCTION
    RIGHT
        (
            IN_STRING VARCHAR(4000),
            RIGHT_OFFSET INT
        )
    RETURNS VARCHAR
AS $$
    BEGIN 
        RETURN SUBSTRING(IN_STRING FROM CHAR_LENGTH(IN_STRING) - (RIGHT_OFFSET - 1));
    END;
$$ LANGUAGE PLPGSQL;

Adapted from @ta.speot.is's answer.

Upvotes: 1

ta.speot.is
ta.speot.is

Reputation: 27214

I'm assuming psql is PostgreSQL. If that's the case, you should read the PostgreSQL documentation describing the string functions that are available to you.

right is not one of them.

Try substring(cc_number from char_length(cc_number) - 3).

In future you may want to use Google to help answer questions like this. Google is a search engine; you can use search engines to find documentation; documentation tells you how to use a product.

Upvotes: 7

Related Questions