I have a table like this: Id Description Recipient 1 lipsum 35235 2 dolor est 123, 456, 2432 3 Lorem Ipsum 143243, 34, 2344 And I'd like an output like this: Id Description Recipient RecipientId 1 lipsum 35235 35235 2 dolor est 123, 456, 2432 123 3 Lorem Ipsum 143243, 34, 2344 143243 I need to join a table of

Return first element of each cell

I have a table like this:

Id	Description	Recipient
1	lipsum	35235
2	dolor est	123, 456, 2432
3	Lorem Ipsum	143243, 34, 2344

And I’d like an output like this:

Id	Description	Recipient	RecipientId
1	lipsum	35235	35235
2	dolor est	123, 456, 2432	123
3	Lorem Ipsum	143243, 34, 2344	143243

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?

Answer

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.

Advertisement

Answer