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.
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