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