Want to split the string after comma

Transact-SQL
1

i came across the scenario where user will upload the files(.png,.jpg etc) more than one at same time. when i want to take count of each file am facing difficulty.

below is table

declare @tab1 table
(
filenames NVARCHAR(MAX)
)

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

2

hope this helps :slight_smile:

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

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

select filenames,count(filenames) from @tab1 group by filenames

image

3

Dear harishgg1,

Thanks for your time,
in real time i do not get as you inserted data in the table.

the main problem am facing over here is split of files and get count of each file.

Regards,

4

by "get count of each file" do you mean .png, .jpg?

depending on what version of SQL You have you could leverage json functions

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

insert into @tab_json(filenames) values('[{"file_name":"7HB1ms8g6y1AKGb4ucLqZPXy9H1vqWVj.png","file_type":"png"}]')
insert into @tab_json(filenames) values('[{"file_name":"NNjK3Jv34yNF0EL7OVRIsKG8PWbUtRdN.jpg","file_type":"jpg"}]')
insert into @tab_json(filenames) values('[{"file_name":"K1knhuDq2OSnwQuzTF7cHhXHbv9xvDGs.png","file_type":"png"}]')
insert into @tab_json(filenames) values('[{"file_name":"NNjK3Jv34yNF0EL7OVRIsKG8PWfedss.jpg","file_type":"jpg"}]')
insert into @tab_json(filenames) values('[{"file_name":"K1knhuDq2OSnwQuzTF7cHhXHbhjsuns.png","file_type":"png"}]')
insert into @tab_json(filenames) values('[{"file_name":"K1knhuDq2OSnwQuzTF7cHhXHbhjsuns.png","file_type":"png"}]')
--ALTER DATABASE sqlteam SET COMPATIBILITY_LEVEL = 130


select FileType, count(1)	   
  from @tab_json c 
 CROSS APPLY OPENJSON(c.filenames)

WITH (FileName nvarchar(max) '$.file_name',
      FileType nvarchar(255) '$.file_type'
	  ) as jsonValues
group by FileType
1 Like
5

Thank you yosiasz.

It helped me :slightly_smiling_face:

6

If same i want to use in AWS then am getting below error how can i resolve it ?

Msg 319, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

7

put a semi-colon in front of the word With

8

Hi Mike01,

Can you please edit in the below code.

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

insert into @tab_json(filenames) values('[{"file_name":"7HB1ms8g6y1AKGb4ucLqZPXy9H1vqWVj.png","file_type":"png"}]')
insert into @tab_json(filenames) values('[{"file_name":"NNjK3Jv34yNF0EL7OVRIsKG8PWbUtRdN.jpg","file_type":"jpg"}]')
insert into @tab_json(filenames) values('[{"file_name":"K1knhuDq2OSnwQuzTF7cHhXHbv9xvDGs.png","file_type":"png"}]')
insert into @tab_json(filenames) values('[{"file_name":"NNjK3Jv34yNF0EL7OVRIsKG8PWfedss.jpg","file_type":"jpg"}]')
insert into @tab_json(filenames) values('[{"file_name":"K1knhuDq2OSnwQuzTF7cHhXHbhjsuns.png","file_type":"png"}]')
insert into @tab_json(filenames) values('[{"file_name":"K1knhuDq2OSnwQuzTF7cHhXHbhjsuns.png","file_type":"png"}]')
--ALTER DATABASE sqlteam SET COMPATIBILITY_LEVEL = 130

select FileType, count(1)
from @tab_json c
CROSS APPLY OPENJSON(c.filenames)

WITH (FileName nvarchar(max) '.file_name', FileType nvarchar(255) '.file_type'
) as jsonValues
group by FileType

9

same code is working fine in azure but not in AWS

10

Azure aws sql server?

11

Yes,but I have no idea why this code is returning error.

Any other way to achieve the same result ??

12

Same version of sql?
Did you run this?
ALTER DATABASE sqlteam SET COMPATIBILITY_LEVEL = 130

13

I get this when I run your code

Msg 13607, Level 16, State 3, Line 15
JSON path is not properly formatted. Unexpected character '.' is found at position 0.

I modified your query and this is now working:

select FileType, count(1)
from #tab_json c
CROSS APPLY OPENJSON(filenames)
	WITH (FileName nvarchar(max) '$.file_name', 
		  FileType nvarchar(255) '$.file_type'
		 ) as jsonValues
group by FileType
1 Like
14

Wonder if the dollar sign was being stripped off when he posted his

$.file_type

nope works

15

Missing dollar sign was an typo mistake.

Am getting error saying "valid values of the database compatibility level are 100,110 and 120" so am not able to make it to 130.

16

what version of SQL Server is it?

17

Microsoft SQL server 2014

18

@yosiasz and @mike01 ,

I am still seeking help from you guys on this topic !!

Thanks

19

Time to upgrwde to 2016. 130 is sql 2016 onward.
so you have two separate version of sql for the same database?

20

JSON is only available in SQL 2016 and higher. It's not supported in 2014