I have a table like this:

And I'd like an output like this:

I need to join a table of recipient data using the recipient IDs. Thankfully, the necessary data will be the same for every recipient in the row, so I only need one ID. I want to return the same data, except with only the first (or even just one) ID for each row in the recipient column.

My current method is like this:

SELECT Id,
       Description,
       Recipient,
       MAX(value) as RecipientID
FROM msg 
CROSS APPLY STRING_SPLIT(Recipient, ',')
GROUP BY Id, Description, Recipient

While this method gives me a single recipient ID that I can then use as a key, it takes quite a long time since the recipient column could have a list of IDs larger than 2k in one cell.

I have tried a REGEX solution to extract the starting set of numbers in front of the delimiter, but I couldn't find a way to do it that wasn't being used as a filter.

It'd be great if SQL Server had a function like MySQL's SUBSTRING_INDEX to just get the first ID, but it doesn't.

How could I go about only returning the one element of each recipient cell without having to do a CROSS APPLY and aggregating?

CodePudding user response：

You could use the base string functions as follows:

SELECT Id, Description, Recipient,
       SUBSTRING(Receipient   ',', 1, CHARINDEX(',', Recipient   ',') - 1) AS RecipientId
FROM msg;

This answer uses a trick by adding a comma to the end of the Recipient, so that CHARINDEX will always be able to find it.

CodePudding user response：

(SELECT * FROM STRING_SPLIT(Recipient, ', ') Limit 1) as RecipientId