POSTGRES - add whitespace

Question

I have a column called item and I would like to add whitespace at the START of each row in the item column.

I would also like to remove the 3 and insert the result into a new column called item_n.

item                 item_n
-------------------|-----------
to go 3            |
1 2 3              |
cat dog 3          |
blah blah 3

RESULT:

item          |  item_n
------------------------------
to go 3       | to go
1 2 3         | 1 2
cat dog 3     | cat dog
blah blah 3   | blah blah

Thanks in advance

Edit your question and show the results that you want.

Gordon Linoff
– Gordon Linoff

2018-03-05 02:45:21 +00:00
Commented Mar 5, 2018 at 2:45 — Gordon Linoff
– Gordon Linoff, Commented Mar 5, 2018 at 2:45
Your title and your sample data don't really make sense.

Gordon Linoff
– Gordon Linoff

2018-03-05 02:54:22 +00:00
Commented Mar 5, 2018 at 2:54 — Gordon Linoff
– Gordon Linoff, Commented Mar 5, 2018 at 2:54

Aravindhan R · Accepted Answer · 2018-03-05 06:59:55Z

1

Try this it will give you the perfect answer

Select concat(' ', replace(item,' 3',' ')) from table name

edited Mar 5, 2018 at 6:59

user330315

answered Mar 5, 2018 at 3:10

Aravindhan R

2686 silver badges26 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2018-03-05 03:11:32Z

0

You can just use left() with a negative second argument:

select left(item, -2) as item_n

If you know that the " 3" only appears at the end of the string, you can do:

select replace(item, ' 3', '')

Or, if the " 3" might not be in the string, then case is one solution:

select (case when item like '% 3' then left(item, -2) else item
        end) as item_n

You can prepend white space by using string concatenation:

select ' ' || left(item, -2) as item_n

edited Mar 5, 2018 at 3:11

answered Mar 5, 2018 at 2:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

5 Comments

Paul Over a year ago

Thanks, how do I create the whitespace at the start of all rows in item_n?

Gordon Linoff Over a year ago

@Paul . . . You can concatenate spaces, but I don't understand what you mean.

Paul Over a year ago

I know it's hard to see from my example but in the item_n column in the results there is whitespace at the front

Aravindhan R Over a year ago

@GordonLinoff Nice answer with different solutions . Thank you

Paul Over a year ago

Thanks for you help!

Collectives™ on Stack Overflow

POSTGRES - add whitespace

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related