Question 89793 in PostgreSQL Question posted in
The official documentation can be found here.

SQL insert string from one column into another in select statement – Postgresql

I have a SQL table that looks something like this:

   Date          Object_ID           Category   Direction

0  2011-02-02    0H 1234 JKL/987        A          N
1  2011-02-02    0H 4321 BNM/987        A          N
2  2011-02-02    0H 5678+ JKL/987       A          N
3  2011-02-02    0H 8765 BNM/987        A          S
4  2011-02-02    0H 9021+ JKL/987       A          S
5  2011-02-02    0H 1102+ JKL/987       A          N

I want to be able to add the string value in the ‘Direction’ column (either ‘N’ or ‘S’) to the ‘Object_ID’ column at a specific position so that the output of the select statement returns this:

   Date          Object_ID           Category   Direction

0  2011-02-02    0H 1234 NJKL/987       A          N
1  2011-02-02    0H 4321 NBNM/987       A          N
2  2011-02-02    0H 5678+NJKL/987       A          N
3  2011-02-02    0H 8765 SBNM/987       A          S
4  2011-02-02    0H 9021+SJKL/987       A          S
5  2011-02-02    0H 1102+NJKL/987       A          N

I know that the spacing is odd but it’s important that it is maintained. Any help would be appreciated.

Tags:

2

Answers


  1. 0

    Given the example, where data are exactly in 2 different formats based on something in the 8th position, then you can use a case expression with concat().

    with my_data as (
  select '2011-02-02' as date, '0H 1234 JKL/987' as object_id, 'A' as category, 'N' as direction union all
  select '2011-02-02', '0H 4321 BNM/987', 'A', 'N' union all
  select '2011-02-02', '0H 5678+ JKL/987', 'A', 'N' union all
  select '2011-02-02', '0H 8765 BNM/987', 'A', 'S' union all
  select '2011-02-02', '0H 9021+ JKL/987', 'A', 'S' union all
  select '2011-02-02', '0H 1102+ JKL/987', 'A', 'N')
select date, object_id as orig_obj_id, 
 case 
  when substring(object_id, 8, 1) = ' ' 
    then concat(substring(object_id, 1, 8), direction, substring(object_id, 9, 8))
    else concat(substring(object_id, 1, 8), direction, substring(object_id, 10, 7))
  end as mod_obj_id, 
 category, direction
from my_data;
    date orig_obj_id mod_obj_id category direction
    2011-02-02 0H 1234 JKL/987 0H 1234 NJKL/987 A N
    2011-02-02 0H 4321 BNM/987 0H 4321 NBNM/987 A N
    2011-02-02 0H 5678+ JKL/987 0H 5678+NJKL/987 A N
    2011-02-02 0H 8765 BNM/987 0H 8765 SBNM/987 A S
    2011-02-02 0H 9021+ JKL/987 0H 9021+SJKL/987 A S
    2011-02-02 0H 1102+ JKL/987 0H 1102+NJKL/987 A N

    Output easier seen as text vs table above:

    mod_obj_id
0H 1234 NJKL/987
0H 4321 NBNM/987
0H 5678+NJKL/987
0H 8765 SBNM/987
0H 9021+SJKL/987
0H 1102+NJKL/987
    Login or Signup to reply.
  2. 0

    Using regexp_replace:

    select t.rnum, t.date, regexp_replace(t.object_id, '(?<=d)+*s(?=[A-Z])', 
     case when regexp_substr(t.object_id, '(?<=d)+*s(?=[A-Z])') = ' ' 
          then '  '||t.direction else '+'||t.direction end), 
     t.category, t.direction 
from tbl t

    See fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search