CODEWITHSUNDEEP
sqlpostgresqlselectsubstring
slowmotionnovember
slowmotionnovember

Reputation: 13

SQL insert string from one column into another in select statement

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.

Upvotes: 0

Views: 189

Answers (2)

Ajax1234
Ajax1234

Reputation: 71461

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.

Upvotes: 0

Isolated
Isolated

Reputation: 6454

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

Upvotes: 0

Related Questions