Pick up string between nth occurrence of + and nth+1 occurrence of + SQL

Question

I am trying to fetch string between third + and fourth + in SQL query

example is from string =="'Secondary - WA / 010 - 8104 + R + VET + 14.10.2022 - must'"

I need final answer as VET , pick up anything after second + and third +

Answer 1

You can try like this:

Declare @text varchar(100) = 'Secondary - WA / 010 - 8104 + R + VET + 14.10.2022 - must'
Declare @2nd int
Declare @3rd int

Select @2nd = charindex('+',@text,charindex('+',@text)+1)
Select @3rd = charindex('+',@text,@2nd+1)
Select Substring(@text, @2nd+1, @3rd-@2nd-1)

FIDDLE DEMO

Answer 2

To extract a string between the third and fourth + characters in a given string in Microsoft SQL Server, you can use the SUBSTRING and CHARINDEX functions as follows:

DECLARE @string VARCHAR(100) = 'Secondary - WA / 010 - 8104 + R + VET + 14.10.2022 - must';

SELECT SUBSTRING(@string, CHARINDEX('+', @string, CHARINDEX('+', @string, CHARINDEX('+', @string) + 1) + 1) + 1, CHARINDEX('+', @string, CHARINDEX('+', @string, CHARINDEX('+', @string) + 1) + 1) - CHARINDEX('+', @string, CHARINDEX('+', @string, CHARINDEX('+', @string) + 1) - 1) AS result;

The CHARINDEX function is used to find the positions of the + characters in the string. The SUBSTRING function is then used to extract the substring between the third and fourth + characters.

This will return the result VET.

Note that this approach assumes that the string always contains at least four + characters. If the string does not contain enough + characters, the query may return an error or an unexpected result.

Answer 3

The only way that came to my mind to achieve it, is using PL/SQL, you can use the function INSTR to find the index of the nth '+' or any char, then the SUBSTRING function with your input string and the index of your two characters to get the string you want. Here is an example :

SET SERVEROUT ON;
DECLARE
    string VARCHAR(100) := 'Secondary - WA / 010 - 8104 + R + VET + 14.10.2022 - must';
    fist INTEGER;
    last INTEGER;
BEGIN
    first := INSTR(string, '+', 1, 2);
    last := INSTR(string, '+', 1, 3);
    DBMS_OUTPUT.PUT_LINE(SUBSTR(string, first, last - first));
END;