Search code examples
postgresql

Remove last n chars of a varchar column

I wanted to know if there's a "compact" way of deleting last n chars from a column in PostGreSQL.

I have a column born as a DATE formatted like this: yyyy-MM-dd.

I altered the table to make this column a varchar, replaced all of the unnecessary dashes but I can't think about a quick and reliable way to delete the last two chars since I'd like the varchar to show only yyyyMM.

With "quick and reliable" I mean something that won't ask me to use a temp table.

Thanks in advance

Solution

  • Use left(), e.g.:

    select 
    left('20181004', 6),    -- get 6 leftmost characters
    left('20181004', -2)    -- or remove 2 last ones

  left  |  left  
--------+--------
 201810 | 201810
(1 row)