PostgreSQL update statement to substring a column value

Question

Considering XPT table containing the following values:

123, 443, 213, 124

I want to update it remove the last character of each number in order to get it like this:

12, 44, 21, 12

Please read stackoverflow.com/help/how-to-ask. Tell us what you already tried and what is not working. — Alexandre Cartapanis
– Alexandre Cartapanis, Commented Jun 6, 2016 at 14:16

Jaumzera · Accepted Answer · 2016-06-06 16:49:27Z

1

Use SUBSTRING() function:

SELECT SUBSTRING(t.value, 1, 2)
    FROM XPT t;

Or if you want to update:

UPDATE XPT t
    SET t.value = SUBSTRING(t.value, 1, 2);

Jaumzera

2,4292 gold badges34 silver badges47 bronze badges

answered Jun 6, 2016 at 14:08

sagi

40.6k7 gold badges66 silver badges87 bronze badges

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

6 Comments

its possible using inner join this substring?

not working select substring (nuts3_resid_1amc,1,2) as source from dados inner join nuts02 on (dados.source= nuts02.id) where nuts3_resid_1amc='117'

ERROR: column dados.source does not exist

So you don't have a column source in table dados.. the error is pretty clear. @user2964140

Don't you have an ID column connects to nuts02 table ?

|