SQL Server String manipulation column to new column

Question

Need to create new column from the existing column with string manipulation on the fly

Existing stored string in the column is nvarchar

Existing column string example value 0001134564444

Need, the first three positions '000' to become 'AB' (there is a good number of variations)

the following 11 shall be 1 (there are eight variations results (1 to 8))

the following 3456 shall take the last two characters 56

the last 4444 shall not change.

The new string should then be AB5614444

Now need this done in SQL server,...

have tried with, substring, stuff, replace, charindex Have reached the point...never too late to give up, this is my last chance.

Oracle version,

select column NewColumn, decode(substr(column, 1,3),'000','AB', '010', 'BC' ...)||substr(column,8,2)
||substr(column,5,1)||substr(column,10,5) NewStringColumn from table

getting absolutely nowhere with SQL server for this task, oracle remains no longer an option. any help is appreciated,

Please add sample input and output data.

Tim Biegeleisen
– Tim Biegeleisen

2020-06-07 08:02:36 +00:00
Commented Jun 7, 2020 at 8:02 — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 7, 2020 at 8:02
Shouldn't the result be AB1564444?

sacse
– sacse

2020-06-07 08:12:42 +00:00
Commented Jun 7, 2020 at 8:12 — sacse
– sacse, Commented Jun 7, 2020 at 8:12

GSerg · Accepted Answer · 2020-06-07 08:23:27Z

1

select
  case substring(c, 1, 3) when '000' then 'AB' when '010' then 'BC' ... end
  +
  case substring(c, 4, 2) when '11' then '1' ... end
  +
  substring(c, 8, 6)

with first_mapping as (
  select k, v
  from (values
    ('000', 'AB'),
    ('010', 'BC'),
    ...
  ) v(k, v)
),
second_mapping as (
  select k, v
  from (values
    ('11', '1'),
    ...
  ) v(k, v)
)
select
  (select v from first_mapping where k = substring(c, 1, 3))
  +
  (select v from second_mapping where k = substring(c, 4, 2))
  +
  substring(c, 8, 6)

edited Jun 7, 2020 at 8:23

answered Jun 7, 2020 at 8:15

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

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

Collectives™ on Stack Overflow

SQL Server String manipulation column to new column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related