Want to split the string after comma

Transact-SQL
21

another bootleg option is as follows. Research DelimitedSplit8K

use sqlteam
go

declare @tab table
(
id int identity(1,1),
filenames NVARCHAR(MAX)
)

insert into @tab(filenames) values('[{"file_name":"7HB1ms8g6y1AKGb4ucLqZPXy9H1vqWVj.png","file_type":"png"}]')
insert into @tab(filenames) values('[{"file_name":"NNjK3Jv34yNF0EL7OVRIsKG8PWbUtRdN.jpg","file_type":"jpg"}]')
insert into @tab(filenames) values('[{"file_name":"K1knhuDq2OSnwQuzTF7cHhXHbv9xvDGs.png","file_type":"png"}]')
insert into @tab(filenames) values('[{"file_name":"NNjK3Jv34yNF0EL7OVRIsKG8PWfedss.jpg","file_type":"jpg"}]')
insert into @tab(filenames) values('[{"file_name":"K1knhuDq2OSnwQuzTF7cHhXHbhjsuns.png","file_type":"png"}]')
insert into @tab(filenames) values('[{"file_name":"K1knhuDq2OSnwQuzTF7cHhXHbhjsuns.png","file_type":"png"}]')

;with src
as
(
	select replace(b.Item,'}]','') as filetype
	from @tab a

	cross apply DelimitedSplit8K(a.filenames, ':') b
	where b.ItemNumber = 3
)

select filetype, count(1)
From src
group by filetype
22

Azure is for Test and Dev server where as AWS is used for production.

23

Actually it is recommended that both of your dev and prod be on same version. then you don't get surprises like this.

24

in trouble with the below input returning incorrect count

insert into @tab(filenames) values('[{"file_name":"YCI4VFUpUikCTLCqfvYFGsqa5aCrlpAl.jpg","file_type":"jpg"},
{"file_name":"BUyVGwubG1yHKHvJfdMBckDdUajSEtO9.png","file_type":"png"},
{"file_name":"yOFT4l67IPx7RA25U2bPf8wqRS1ifCcW.png","file_type":"png"},
{"file_name":"UvAqKqIKiQWFDzdTeYjO2Jbo21AZhBss.jpg","file_type":"jpg"},
{"file_name":"id7mpHjwHkvmVx8z1pENbiqHBI85Fn4J.png","file_type":"png"},
{"file_name":"B2RxpgclcpAuhjjEqkL8Cw6U4uiPldMD.png","file_type":"png"}]')

insert into @tab(filenames) values('[{"file_name":"NNjK3Jv34yNF0EL7OVRIsKG8PWbUtRdN.jpg","file_type":"jpg"}]')
insert into @tab(filenames) values('[{"file_name":"K1knhuDq2OSnwQuzTF7cHhXHbv9xvDGs.png","file_type":"png"}]')
insert into @tab(filenames) values('[{"file_name":"NNjK3Jv34yNF0EL7OVRIsKG8PWfedss.jpg","file_type":"jpg"}]')
insert into @tab(filenames) values('[{"file_name":"K1knhuDq2OSnwQuzTF7cHhXHbhjsuns.png","file_type":"png"}]')
insert into @tab(filenames) values('[{"file_name":"K1knhuDq2OSnwQuzTF7cHhXHbhjsuns.png","file_type":"png"}]')

25

please be a bit more specific. What did you use from all of the scripts that were provided to you and what incorrect count are you seeing?

26

you have inconsistent data.
String parsing will come to byte you, just upgrade your SQL Server if you want to benefit fully from JSON feature because your data is json.

;with src
as
(
	select *
	from @tab a

	cross apply DelimitedSplit8K(a.filenames, ',') b
)

select count(1), replace(replace(b.Item,'}',''),']','')
From src
cross apply DelimitedSplit8K(Item, ':') b

where src.Item like '%file_type%'
 and b.ItemNumber = 2
 group by replace(replace(b.Item,'}',''),']','')

uhum @JeffModen

1 Like
27

Thanks a lot guys for your efforts @yosiasz , @harishgg1 and @mike01

and especially @yosiasz :slight_smile:

28

Guys i have another requirement on same but this time i do not want show count instead json data itself.

i tried doing it but facing duplicate record issue.

declare @tab table
(
jsonid int identity(1,1),
empid int,
jsonLinks nvarchar(max)
)

declare @temp table
(
id int identity(1,1),
empid int,
worklocation varchar(255),
AttendedDate date,
supervisor varchar(255),
Manager varchar(255),
Remarks varchar(255)
)

insert into @temp(empid,worklocation,AttendedDate,supervisor,Manager,Remarks)
values(1,'NewYork','2020-02-01','mike','ching chang',null)
insert into @temp(empid,worklocation,AttendedDate,supervisor,Manager,Remarks)
values(2,'NewYork','2020-02-02','tyson','Adam',null)
insert into @temp(empid,worklocation,AttendedDate,supervisor,Manager,Remarks)
values(1,'NewYork','2020-02-02','mike','ching chang',null)

insert into @tab(empid,jsonLinks)
values(1,'[{"file_name":"9EnzA3EoRo2rXnsBhusJJYuqenLI1G3m.png","file_type":"png"}]')
insert into @tab(empid,jsonLinks)
values(1,'[{"file_name":"QN2XCzonsG5uaXpubVDnJoxwmr9IVy6m.jpg","file_type":"jpg"},
{"file_name":"hTP04FHuWSFqKwKpaKcvyECnoUiiNLGb.png","file_type":"png"},
{"file_name":"QvxU1WMoG4pUykLBc83oSfT1RyT88yzk.png","file_type":"png"},
{"file_name":"n2cJ8E3BidA6FheEi7SNYZaU7VyWR7CA.jpg","file_type":"jpg"},
{"file_name":"sMyHyHJELDmvJGQEwns4bAjyhqusXk2g.jpg","file_type":"jpg"},
{"file_name":"wtMV3xnx1dFdl1XvHItPw8yGF9xNTnTQ.jpg","file_type":"jpg"},
{"file_name":"QqBpwaVLUEhj6iCZzC2rt8eFzT8SdMEI.jpg","file_type":"jpg"},
{"file_name":"Ng6RFKPep9Ium5D38KZ992Txa9gvMOdN.jpg","file_type":"jpg"}]')
insert into @tab(empid,jsonLinks)
values(2,'[{"file_name":"n79zmC7IW9NzhxLLDY6aIDasPgrZ7q3n.png","file_type":"png"},
{"file_name":"OwjRpCPhyu71hGekWrvTiqkuIWkjRLiR.png","file_type":"png"}]')

;with cte as
(
select -- distinct Row_number() over(partition by t.AttendedDate order by t.AttendedDate) as rowid,
t.AttendedDate,
t.empid ,
t.worklocation,

    t.supervisor ,
    t.Manager,
    t.Remarks,
	tb.jsonLinks

from @temp t inner join @temp t2 on t.AttendedDate = t2.AttendedDate
and t.empid = t2.empid
inner join @tab tb
on t.empid = tb.empid
--inner join @temp t2 on t.AttendedDate = t2.AttendedDate
where t.empid = 1

)
select distinct empid,AttendedDate,worklocation,supervisor,Manager,Remarks,jsonLinks
from cte
group by AttendedDate,empid,worklocation,supervisor,Manager,Remarks,jsonLinks

29

There are 2 EmpID = 1 in each table, since you are joining on empID, you will get 4 rows. How do you distinguish one from the other?

30

Hi mike01,

Thanks for the reply

Based on column attendeddate.
Since same employee has attended same location in two different days.

M expecting only two records.

31

Since there is nothing in the @Tab table to identify the AttendedDate, you're pretty much stuck.

1 Like