Get first four comma-separated values in SQL Server 2012

Question

If I have a column with 16 comma separated values, I only want to select the first four comma-separated values. I am using an older SQL Server so string_split is not an option.

I have an idea how to get the first three values but not four.

Example:

f,a,bcd,gd,er,ad,adsadc,asdf,rte,fer,ter,657,4545,sf,ret,tyu

Goal: to get

f,a,bcd,gd

I don't mind keeping the commas in the substring

There is some code online about how to find the first three but I can't necessarily comprehend it.

select 
    substring(Column1, 1, charindex(",", Column1, (charindex(",", Column1, (charindex(",", Column1, 1) + 1)) + 1)) - 1)

This is gonna be painful and slow. It's NEVER a good idea to put comma-separated data into a column. The best option here will be to **fix** the schema (because it really is _broken_), probably with an additional table. — Joel Coehoorn, Oct 28 '22 at 14:47

Stuck at 1337 · Answer 1 · 2022-10-28T14:38:48.257

In a version that old, your options are pretty limited. Borrowing from this answer:

DECLARE @str varchar(255) = 'f,a,bcd,gd,er,ad,adsadc,...,.,,.,.';

SELECT CONCAT(
  c.value('(/x/r[1]/text())[1]', 'sysname'), ',',
  c.value('(/x/r[2]/text())[1]', 'sysname'), ',',
  c.value('(/x/r[3]/text())[1]', 'sysname'), ',',
  c.value('(/x/r[4]/text())[1]', 'sysname')
)
FROM 
(
  SELECT TRY_CONVERT(xml, CONCAT(
    '<x><r><![CDATA[',
    REPLACE(@str, ',', ']]></r><r><![CDATA['),
    ']]></r></x>'
    )
)) AS t(c);

So, you could put this in an inline table-valued function:

CREATE FUNCTION dbo.GetFirstFourElements
(
  @str varchar(255),
  @sep char(1)
)
RETURNS TABLE
AS
  RETURN (
    SELECT FourElements = CONCAT(
      c.value('(/x/r[1]/text())[1]', 'sysname'), @sep,
      c.value('(/x/r[2]/text())[1]', 'sysname'), @sep,
      c.value('(/x/r[3]/text())[1]', 'sysname'), @sep,
      c.value('(/x/r[4]/text())[1]', 'sysname')
    )
    FROM 
    (
      SELECT TRY_CONVERT(xml, CONCAT(
        '<x><r><![CDATA[',
        REPLACE(@str, @sep, ']]></r><r><![CDATA['),
        ']]></r></x>'
        )
    )) AS t(c)
);

And then either call it against a variable:

DECLARE @str varchar(255) = 'f,a,bcd,gd,er,ad,adsadc,...,.,,.,.';

SELECT FourElements 
  FROM dbo.GetFirstFourElements(@str, ',') AS f;

Or against a table:

DECLARE @t table(str varchar(255));

INSERT @t VALUES('a,b,c,d,e,f,g'),
  ('f,a,bcd,gd,er,ad,adsadc,...,.,,.,.');

SELECT t.str, f.FourElements 
  FROM @t AS t
  CROSS APPLY dbo.GetFirstFourElements(t.str, ',') AS f;

Working example in this fiddle (except that is shown in SQL Server 2014, since even they don't support ancient versions of SQL Server anymore).

Given the new information ("I can't use user-defined functions, and I don't have any tables"), here's how you can do this from a variable, without a function, and no tables:

DECLARE @str varchar(255) = 'f,a,bcd,gd,er,ad,adsadc,...,.,,.,.';

SELECT FourElements = CONCAT(
  c.value('(/x/r[1]/text())[1]', 'sysname'), ',',
  c.value('(/x/r[2]/text())[1]', 'sysname'), ',',
  c.value('(/x/r[3]/text())[1]', 'sysname'), ',',
  c.value('(/x/r[4]/text())[1]', 'sysname')
)
FROM 
(
  SELECT TRY_CONVERT(xml, CONCAT(
    '<x><r><![CDATA[',
    REPLACE(@str, ',', ']]></r><r><![CDATA['),
    ']]></r></x>'
    )
)) AS t(c);

So Im not able to use any user defined functions or tables, I wanted to just edit what I have above in the question using only substring and charindex etc. I want to eventually use that overall substring in a window function for something else — Soaps, Oct 28 '22 at 14:02
@Soaps Why are you not allowed to use functions? Also, your data isn't in tables? — Stuck at 1337, Oct 28 '22 at 14:36

Get first four comma-separated values in SQL Server 2012

1 Answers1