How to split a multiple string using t-sql

Question

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:

MakeModelColor
Apple - iphone 12
Apple - iphone 12 pro max - black -128gb
Samsung - galaxy A12

This is the result I am looking for:

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

Actual result am looking for ](https://i.sstatic.net/BvzYN.png)

For some tips on how you could improve your question see How to ask, tips-for-asking-a-good-structured-query-language-question and how to provide a Minimal, Reproducible Example and how not to use pictures of data, code or errors — Stu
– Stu, Commented Nov 30, 2022 at 23:23
Aside: It's best not to try to bend ParseName into doing other things. Give it a string longer than a sysname or a string with more than three dots and it fails hard. Try munging other separators into dots and something else will break, e.g. 'Bedlam - GerbilFone v.2 - paisley' has a dot in the data. — HABO
– HABO, Commented Dec 1, 2022 at 3:48

John Cappelletti · Accepted Answer · 2022-12-01 00:01:30Z

4

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

edited Dec 1, 2022 at 0:01

answered Nov 30, 2022 at 23:35

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

Comments

Cetin Basoz · Accepted Answer · 2022-11-30 23:31:54Z

2

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

answered Nov 30, 2022 at 23:31

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

2 Comments

Timothy Obidike Over a year ago

Thanks for your input but but my t-sql does not recognise string_split command because of the version but issue is resolved with @john cappelleti solution

Cetin Basoz Over a year ago

@TimothyObidike, then you should mark that as answer. I wonder which version is that having JSON_VALUE but not STRING_SPLIT. AFAIK both are available since SQL Server 2016 (13.x) - ordinal parameter was not supported initially.

Collectives™ on Stack Overflow

How to split a multiple string using t-sql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related