Pick up string between nth occurrence of and nth 1 occurrence of SQL-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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;