Hello, good morning colleagues.
I currently have a text string as follows:
registro1\nregistro2\nregistro3\nregistro4\nregistro5....
From all that chain I only need to extract record 5, through a Loop the count is made of how many times the character \n appears, so with the use of the substring statement I can identify where record 5 begins:
DECLARE @str NVARCHAR(MAX) = N'registro1\nregistro2\nregistro3\nregistro4\nregistro5\registro6';
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, @p+2,16) ELSE @str END;
The query already throws me the beginning of the record, but when trying to use CHARINDEX specifying the following character \n and so it only shows me record 5 it gives me a blank value, doing the test and entering an integer value as shown in the example works no problem, the issue is that since the records will always have different sizes, I need it to be able to detect the character that separates the records.
I also tried with the LEN() function and subtracting the characters with the CHARINDEX function but it didn't work for me either
It doesn't seem at all necessary to loop to extract a part of a string.
There are many functions of the type that I am going to give you that perform this operation.
Delimited Split
It can not be easier.
Here the code of the function:
loop option
If you need to do it in a loop, the way you were doing it, you can use one more loop to get to the character
\
and lock in its position using another helper variable.So
@p
you will have the separator position "5" and@pAux
you will have the separator position "4"