I have tried to split this string but unsuccessful because of the way the string is arranged in the column. The Null value keeps appearing on the Make and model column and the actually data goes to the wrong column

sample data:

result am looking for

Actual result am looking for ](https://i.stack.imgur.com/BvzYN.png)

CodePudding user response：

That could be done in a number of ways. ie:

with data(makeModelColor, part, ordinal) as (
  select makeModelColor, ltrim(rtrim(value)), ordinal
from devices
cross apply (select * from String_Split(devices.makeModelColor,'-',1)) t)
select makeModelColor,
  max(case when ordinal = 1 then part end) as Make,
  max(case when ordinal = 2 then part end) as Model,
  max(case when ordinal = 3 then part end) as Color,
  max(case when ordinal = 4 then part end) as Other
  from data
group by makeModelColor;

DBFiddle demo

CodePudding user response：

Just another option using a bit of JSON

Example

 Select Make  = trim(JSON_VALUE(JS,'$[0]'))
       ,Model = trim(JSON_VALUE(JS,'$[1]'))
 From  YourTable A
 Cross Apply (values ('["' replace(string_escape([MakeModelColor],'json'),'-','","') '"]') ) B(JS)

Results

Make            Model
Apple           iphone 12
Apple           iphone 12 pro max
Samsung galaxy  A12