SQL insert string from one column into another in select statement

Question

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.

If the reverse of this problem is easier (already having the directional indicator and getting rid of it instead of adding it) I would definitely accept those solutions as well — slowmotionnovember
– slowmotionnovember, Commented Feb 10, 2023 at 15:02

Isolated · Accepted Answer · 2023-02-10 15:14:11Z

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

answered Feb 10, 2023 at 15:14

Isolated

6,4942 gold badges12 silver badges23 bronze badges

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

4 Comments

slowmotionnovember Over a year ago

Looks like it's outputting '0H 1234 N JKL/987' instead of '0H 1234 NJKL/987' for the IDs without a '+'

Isolated Over a year ago

No, it's not. Look at the code above. The output was copied/pasted. If you're seeing otherwise, then your data is different than what you put in the question.

slowmotionnovember Over a year ago

You are correct, I apologize. There is an extra space between the 1234 and JKL/987 that I did not put in the original question. Thank you for your help!

Isolated Over a year ago

If your data indeed has 2 spaces for some, then you'll need to adjust this code. You can replace two spaces with one as part of the process, but really depends on your data and desired output.

Ajax1234 · Accepted Answer · 2023-02-10 15:48:51Z

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.

answered Feb 10, 2023 at 15:48

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Collectives™ on Stack Overflow

SQL insert string from one column into another in select statement

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related