2

The intent of the following algorithm is to extract certain set of fields from a key (in this example, extract first 2 fields), and it works. The fields are delimited by a colon:

declare @key nvarchar (max);
declare @pos int;
declare @fields nvarchar (max);

set @key = 'Field-1:Field-2:Field-3:Field-4:Field-5';
set @pos = charindex(':', @key, charindex (':', @key) + 1);
set @fields = left(@key, @pos - 1);

select @fields;

Result: Field-1:Field-2

Microsoft document says that the first parameter is an expression, but I think what they mean by that in the context of CHARINDEX is, that this expression should evaluate to a string literal; hence the following attempt to pass a RegEx to get the 2nd occurrence doesn’t work; obviously either it is not supported or I am using a bad syntax:

--match the second occurrence of the delimiter using RegEx
set @pos = charindex (':.*?(:)', @key);

In other words, is it possible to find the position of the nth occurrence of the delimiter in a given text using RegEx, so that I could avoid several nested CHARINDEX or a loop to parse? Keeping aside, if n is passed as a parameter, then I can't even use static nesting anymore...

Thanks in advance for the help.

Environment: Microsoft SQL Server 2014 (SP3) Standard Edition (64-bit)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Noble
  • 135
  • 1
  • 11
  • 1
    You want nth sub-string or everything until nth delimiter? – Salman A Aug 29 '19 at 18:48
  • 3
    `PATINDEX` https://learn.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql?view=sql-server-2017 supports pattern search. No Sql-server feature supports RegEx. – Serg Aug 29 '19 at 18:48
  • @SalmanA everything until the nth delimiter – Noble Aug 29 '19 at 18:51
  • 1
    SQL Server is not good at this. You can use a rCTE. Or convert the string to XML, find all nodes until nth node and re-combing using STRING_AGG function. It is clumsy whichever method you use. – Salman A Aug 29 '19 at 18:57
  • @SalmanA Thanks. 2016 has STRING_SPLIT, unfortunately I use 2014! – Noble Aug 29 '19 at 19:11

2 Answers2

1

There is no easy way, only tricks, to extract nth substring of a string. Below is a set based, recursive CTE approach:

DECLARE @str NVARCHAR(MAX) = N'Field-1:Field-2:Field-3:Field-4:Field-5';
DECLARE @num INT = 4;

WITH rcte AS (
    SELECT str = @str
         , n = 1
         , p = CHARINDEX(':', @str, 1)
    UNION ALL
    SELECT str
         , n + 1
         , CHARINDEX(':', str, p + 1)
    FROM rcte
    WHERE n < @num AND p > 0
)
SELECT CASE WHEN p > 0 THEN SUBSTRING(str, 1, p - 1) ELSE str END
FROM rcte
WHERE n = @num;

If loop is an option then:

DECLARE @str NVARCHAR(MAX) = N'Field-1:Field-2:Field-3:Field-4:Field-5';
DECLARE @num INT = 4;
DECLARE @n INT = 0;
DECLARE @p INT = 0;

WHILE 1 = 1
BEGIN
    SET @n = @n + 1;
    SET @p = CHARINDEX(':', @str, @p + 1);
    IF @n = @num OR @p = 0 BREAK;
END;

SELECT CASE WHEN @p > 0 THEN SUBSTRING(@str, 1, @p - 1) ELSE @str END;

DB<>Fiddle

Salman A
  • 262,204
  • 82
  • 430
  • 521
1

Note that I added ":" to the end of the string. Please make that adjustment if necessary.

declare @n int = 5
declare @i int = 0
declare @len int = 0
declare @pos int = 0
declare @c char(1) = ":"
declare @str varchar(256)

set @str = "Field-1:Field-2:Field-3:Field-4:Field-5:"
set @len = len(@str)

while(@i < @n and @pos < @len)
begin
    set @pos = charindex(@c, @str, @pos + 1)
    set @i = @i + 1
end

select substring(@str, 1, @pos -1)
Neeraj Agarwal
  • 1,059
  • 6
  • 5
  • Thanks for the suggestion, but this assumes that all fields are of the same size... I changed one field's size and the algorithm failed! – Noble Aug 30 '19 at 16:37
  • @Noble This does not assume all fields are of the same size. The only assumption is that the fields are separated by ":". – Neeraj Agarwal Aug 30 '19 at 16:48
  • I changed the 3rd field as shown below, and the result came out clipped: set @str = 'Field-1:Field-2:Fields-3:Field-4:Field-5:' Result: Field-1:Field-2:Fields-3:Field-4:Field- – Noble Aug 30 '19 at 16:59
  • 1
    @Noble Thank you pointing it out. I made the necessary correction. I also added a note. – Neeraj Agarwal Aug 30 '19 at 17:19