Home > OS >  Return first element of each cell
Return first element of each cell

Time:12-21

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?

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
  • Related