Convert comma delimited ID list to Comma delimited prefix List SQL Server

Question

I'm trying to convert a comma delimited ID list to an comma delimited Prefix list.

BKR394859607,MTP293840284,SPN489620586

My goal is to convert these 3 Ids in my IDString in SQL Server to

BKR,MTP,SPN

This string can have an infinite number of Ids

Declare @ActivityID as varchar(MAX) = 'BKR394859607,MTP293840284,SPN489620586'
Declare @ActivityPrefixes as varchar(MAX)
Set @ActivityPrefixes = (function to Convert to comma delimited prefix list)

Could you use a temp table, table variable or table valued parameter instead of this delimited list? To deal with this as a string you will have to first split it into a table, then strip off all but the first three characters and finally shove it all back into a delimited list again. — Sean Lange, Jun 22 '16 at 15:11
yes the prefix will always be the first three letters. Also I'm using SQL Server 2014. — GnisPL, Jun 22 '16 at 15:11

score 2 · Accepted Answer · answered Jun 22 '16 at 15:18

As mentioned in comment by Sean Lange, you need to perform three steps

Split the string into individual rows
Strip the required characters
Concatenate the rows back to CSV.

Something like this

DECLARE @ActivityID AS VARCHAR(max) = 
        'BKR394859607,MTP293840284,SPN489620586,GHY489620586' 

SELECT Stuff(split_val, 1, 1, '') as Result
FROM   (SELECT ',' 
               + LEFT(split.a.value('.', 'VARCHAR(100)'), 3) 
        FROM   (SELECT Cast ('<M>' + Replace(@ActivityID, ',', '</M><M>') 
                             + '</M>' AS XML) AS Data) AS A 
               CROSS apply data.nodes ('/M') AS Split(a) 
        FOR xml path(''))a(split_val)

Result : BKR,MTP,SPN,GHY

Vahid Farahmandian · Answer 2 · 2016-06-22T16:09:01.383

1

Try this:

DECLARE @ActivityID AS VARCHAR(MAX) = 'BKR394859607,MTP293840284,SPN489620586';

DECLARE @ActivityPrefixes AS VARCHAR(MAX);

SET @ActivityPrefixes =  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(
         @ActivityID,'0', ''), '1',''), '2', ''),'3', ''), '4',''), '5', ''),
                     '6', ''),'7', ''), '8', ''), '9', '');
SELECT @ActivityPrefixes

If you want to get the first 3 character of each section then try this:

DECLARE @ActivityID AS VARCHAR(MAX) = 'BKR39A859607,M3TP293840284,SP1N4896GG586,BKR394859607,MTP293840284,SPN489620586';

DECLARE @ActivityPrefixes AS VARCHAR(MAX);
SET @ActivityPrefixes = '';

WHILE 1 = 1
BEGIN

    DECLARE @i INT;

    IF CHARINDEX(',', @ActivityID) = 0
        SET @i = 3;
    ELSE
        SET @i = CHARINDEX(',', @ActivityID) - 1;

    IF @ActivityPrefixes = ''
        SET @ActivityPrefixes = SUBSTRING(SUBSTRING(@ActivityID, 1, @i), 1,3);
    ELSE
        SET @ActivityPrefixes = @ActivityPrefixes + ','
            + SUBSTRING(SUBSTRING(@ActivityID, 1, @i), 1, 3);

    IF CHARINDEX(',', @ActivityID) = 0
        BREAK;

    SET @ActivityID = SUBSTRING(@ActivityID,CHARINDEX(',', @ActivityID) + 1,LEN(@ActivityID));
END;

SELECT  @ActivityPrefixes;

edited Jun 22 '16 at 16:09

answered Jun 22 '16 at 15:24

Vahid Farahmandian

6,081
7
42
62

1

This doesn't work if the first 3 characters contains a number OR if any other character is not a number. – Sean Lange Jun 22 '16 at 15:40
@SeanLange Please provide a data example in which this approach does not work – Vahid Farahmandian Jun 22 '16 at 15:43
Works well with the test data I provided and is the simplest. Real Data however contain letters occasionally.. – GnisPL Jun 22 '16 at 15:51
1

Ok try this for the list of values. "BKR39A859607,M3TP293840284,SP1N4896GG586" – Sean Lange Jun 22 '16 at 15:54
Ugh, but now you went to a while loop for something that should be set based. – Sean Lange Jun 22 '16 at 16:15
@SeanLange Set-based or iterative-based operation, the main point is the performance, in which(in this problem) the while is performing better than the set-based operation. – Vahid Farahmandian Jun 22 '16 at 16:19
Huh??? For sample data set of 1 row with 3 values. That is nothing like a real situation. This while loop approach is not going to scale well. – Sean Lange Jun 22 '16 at 16:39

score 1 · Answer 3 · edited May 23 '17 at 10:29

First and foremost, read the following articles, they discuss splitting strings (and reasons not to do it), and compare performance of methods to do it if you can't avoid it.

The upshot of these three articles is (in case the links ever become dead):

Avoid delimited lists as strings where possible, if you need to store a list, a table is much better alternative.
If you have to do it, CLR is the most scaleable (and accurate method).
If you can be certain there are no special XML characters to split, then converting the delimited string to XML then using XQuery to get the individual items works well.
Otherwise, building a tally table using cross joining is the best of the rest.

The most versatile method is the last, since not everyone can use CLR, and guarantee no special XML characters, so the split method for that is:

CREATE FUNCTION [dbo].[Split]
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
(   WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1), (1)) n (N)),
    N2(N) AS (SELECT 1 FROM N1 a CROSS JOIN N1 b),
    N3(N) AS (SELECT 1 FROM N2 a CROSS JOIN N2 b),
    N4(N) AS (SELECT 1 FROM N3 a CROSS JOIN N3 b),
    cteTally(N) AS 
    (   SELECT 0 UNION ALL 
        SELECT TOP (DATALENGTH(ISNULL(@List,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
        FROM n4
    ),
    cteStart(N1) AS 
    (   SELECT t.N+1 
        FROM cteTally t
        WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0)
    )
    SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000)), 
            Position = s.N1,
            ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1)
    FROM cteStart s
);

Now that you have your Split function, you can split your first string:

DECLARE @ActivityID AS VARCHAR(MAX) = 'BKR394859607,MTP293840284,SPN489620586';

SELECT  Item, 
        NewID = LEFT(Item, 3)
FROM    dbo.Split(@ActivityID, ',');

Which gives you:

Item            NewID
-----------------------------
BKR394859607    BKR
MTP293840284    MTP
SPN489620586    SPN

Then you can concatenate this back up using FOR XML PATH():

DECLARE @ActivityID AS VARCHAR(MAX) = 'BKR394859607,MTP293840284,SPN489620586';

SELECT  STUFF(( SELECT  ',' + LEFT(Item, 3)
                FROM    dbo.Split(@ActivityID, ',')
                FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '');

For more on how this works see this answer.

The optimal solution would probably be to have a user defined table type to store string lists:

CREATE TYPE dbo.StringList AS TABLE (Value VARCHAR(MAX));

Then rather than building a delimited string, build up a table:

DECLARE @Activity dbo.StringList;
INSERT @Activity (Value)
VALUES ('BKR394859607'), ('MTP293840284'), ('SPN489620586');

Then you avoid a painful split, and can manipulate each individual record much mor easily.

If you really did need to get a new delimited string, then you can use the same logic as above:

DECLARE @Activity dbo.StringList;
INSERT @Activity (Value)
VALUES ('BKR394859607'), ('MTP293840284'), ('SPN489620586');

SELECT  STUFF(( SELECT  ',' + LEFT(Value, 3)
                FROM    @Activity
                FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '');

score 1 · Answer 4 · edited May 23 '17 at 12:26

1

Use the following answer to implement regeular expression functions in T-SQL. Then you can use the following:

edited May 23 '17 at 12:26

Community

1
1

answered Jun 22 '16 at 15:26

gotqn

42,737
46
157
243

Convert comma delimited ID list to Comma delimited prefix List SQL Server

4 Answers4