Return first element of each cell

Question

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?

please take a look at https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad and normalize your table — nbk, Dec 20 '21 at 18:25
You can use `CHARINDEX()` along with `SUBSTRING()` to do this, e.g. `CASE WHEN CHARINDEX(',', Recipient) > 0 THEN SUBSTRING(Recipient, 1, CHARINDEX(',', Recipient)-1) ELSE Recipient END` - [Example on db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=82f61123fac1cdc38debb777bb6541c6). But honestly, just fix your schema. SQL Already has the ideal structure for storing lists of data, they are called tables. Storing delimited lists is almost never the correct solution. — GarethD, Dec 20 '21 at 18:28
I agree the schema just needs to be updated, but I am just a lowly data analyst and don't have a say in how the database schema is built. I'm just tasked with making reports that work. — Parker.V, Dec 20 '21 at 19:05

Tim Biegeleisen · Accepted Answer · 2021-12-20T19:04:44.640

2

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.

edited Dec 20 '21 at 19:04

answered Dec 20 '21 at 18:28

Tim Biegeleisen

502,043
27
286
360

I was messing around with ways to handle a recipient with just one value. Your approach of just adding a comma at the end is much nicer than anything I came up with. – Mureinik Dec 20 '21 at 18:30

score 0 · Answer 2 · answered Dec 21 '21 at 05:01

Required results can be obtained using Case statement as well

SELECT  [ID],[Description],[Recipient],SUBSTRING([Recipient],0,case CHARINDEX(',',[Recipient])
      when 0 then len([Recipient])
      else  CHARINDEX(',',[Recipient])
      end) AS RecipientID
  FROM [customer].[dbo].[msg ];

In general, it is advised to store the Recipient values in a new table with one to many relationship. Each value is stored in a new row to avoid the use of delimiter. However this query works for your usecase

Wang YinXing · Answer 3 · 2021-12-21T00:53:09.683

-1

(SELECT TOP value FROM STRING_SPLIT(Recipient, ', ')) as RecipientId

edited Dec 21 '21 at 00:53

answered Dec 20 '21 at 18:29

Wang YinXing

278
1
6

6

`substring_index` is a [tag:mysql] function, but the question is tagged with [tag:sql-server] – Mureinik Dec 20 '21 at 18:31
3

LIMIT is not in SQL Server either. – David Browne - Microsoft Dec 20 '21 at 18:50

Return first element of each cell

3 Answers3