How do you format the Substring function in Postgres?

Question

I am trying to execute the following query:

select
    d.ro_number
    ,d.ro_closed_date
    ,d.customer_name
    ,d.veh_year
    ,d.veh_make
    ,d.veh_model
    ,d.op_code_name
    ,r.vin
    ,r.customer_email
    ,r.customer_contact_phone
    , substr(r.customer_contact_phone, 1, 3) as contact_number
from public.declined_work d
    inner join public.ro_closed r
        on d.ro_number = r.ro_number

I am receiving the following error:

ERROR:  function substr(text[], integer, integer) does not exist
LINE 12:  , substr(r.customer_contact_phone, 1, 3) as contact_number
            ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 172

"customer_contact_phone" is data type "text."

I am relatively new to postgres, but to not to SQL in general. I am a bit baffled at what the issue is. I also tried the syntax as in this example

substring('Thomas' from 2 for 3)

But that did not work either.

I tried changing the function to substring as I was told substr doesn't exist in postgres, but I got the same error when I executed this:

select
    d.ro_number
    ,d.ro_closed_date
    ,d.customer_name
    ,d.veh_year
    ,d.veh_make
    ,d.veh_model
    ,d.op_code_name
    ,r.vin
    ,r.customer_email
    ,r.customer_contact_phone
    ,substring(r.customer_contact_phone, 1, 3) as contact_number
from public.declined_work d
    inner join public.ro_closed r
        on d.ro_number = r.ro_number

I thought maybe it is an error due to the data type being text, but that doesn't make any sense to me. The values in that column look like this:

{7147019909, 5246627378}

Thanks in advance.

GMB · Accepted Answer · 2020-05-16 23:22:29Z

2

This works in Postgres:

substring(r.customer_contact_phone, 1, 3) as contact_number

Note that this uses substring() rather than substr() (the latter does not exist in Postgres).

If you want the first three characters, you can also use left():

left(r.customer_contact_phone, 3) as contact_number

The syntax you attempted to use also works:

substring(r.customer_contact_phone from 1 for 3) as contact_number

answered May 16, 2020 at 23:22

GMB

224k25 gold badges103 silver badges151 bronze badges

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

4 Comments

Travis Over a year ago

Sorry, I got the same error with that select substring(r.customer_contact_phone, 1, 3) as contact_number from public.ro_closed r ERROR: function pg_catalog.substring(text[], integer, integer) does not exist LINE 1: select substring(r.customer_contact_phone, 1, 3) as contact_... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 8

GMB Over a year ago

@Travis: it appears that your column is an array of texts, not just a text (that's what text[] stands for). What are you actually trying to do?

Travis Over a year ago

Yeah that's accurate, it's phone numbers and sometimes there is more than one. I am trying to strip off the brackets, the comma and any numbers after the first one that appears.

Travis Over a year ago

I was able to figure it out, thank you. I was not aware I was looking at an array of text, not a text field. I used ARRAY_TO_STRING and was able to do the transformation I needed. Thanks!

Collectives™ on Stack Overflow

How do you format the Substring function in Postgres?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related