Clash Royale CLAN TAG#URR8PPP

SQL Server : Breaking Comma Delimited String and matching values in a table

I am using SQL Server and have a "tags" column that has a string of of comma-separated tags. Is there a way to break the string up and place in another table and have them match to be able to easily see liked tags?

Row 1:

3years_andmore,access_ccc,access_sdl,associate_iii,ccc_tickets,desoto_counter,phone_call__property_tax,ticketing,trainer


Row 2:

3years_andmore,access_ccc,access_sdl,associate_iii,ccc_tickets,desoto_counter,phone_call__dmv,ticketing,trainer


Row 3:

5_minutes,access_ccc,access_sdl,associate_ii,ccc_tickets,desoto_counter,lessthan_3years,phone_call__operations___title_by_mail_inquiry,trainer


Row4:

access_ccc,access_sdl,associate_ii,ccc_customer_request_manager_other,ccc_tickets,desoto_counter,lessthan_3years,phone_call__associate_requesting_manager__customer_requesting_mr_brierton,trainer


There is no real order and not the same tags in field but is there a way to at least sort into a new table and break them up and match to see what tickets have the same tags?

SELECT
[id],
[url],
[external_id],
[type],
[subject],
[description],
[priority],
[status],
[recipient],
[requester_id],
[submitter_id],
[assignee_id],
[organization_id],
[group_id],
[collaborator_ids],
[forum_topic_id],
[problem_id],
[has_incidents],
[due_at],
[tags],
[via],
[custom_fields],
[satisfaction_rating],
[sharing_agreement_ids],
[followup_ids],
[ticket_form_id],
[created_at],
[updated_at],
[channel]
FROM
[Brierton].[dbo].[Tickets]
WHERE
created_at BETWEEN '2017-11-01' AND '2018-08-23'
AND ',' + tags + ',' LIKE '%,' + 'ccc_tickets' + ',%'


1 Answer
1

Since version 2016 there's a built in string_split() function. To get the tags per ticket in a "more relational way" you could use:

string_split()

SELECT
t.id,
x.value
FROM
tickets t
CROSS APPLY
(SELECT value
FROM string_split(t.tags, ',')) x;


From there you could aggregate to get the most used tags.

SELECT
x.value,
COUNT(*)
FROM
tickets t
CROSS APPLY
(SELECT value
FROM string_split(t.tags, ',')) x
GROUP BY
x.value
ORDER BY
COUNT(*) DESC;


db<>fiddle

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.