SQL obtain values between second and third forward slash-CodePudding

In SQL Server, I am trying to obtain the values between the second and third forward slash (/) character. The length of the numbers can vary so substring(column, 8, 10) wouldn't work.

123/123/123456789/12

What I am trying to get in the current example is: 123456789

CodePudding user response：

Please try the following solution based on tokenization.

This method is generic regardless how many tokens are in place.

It will work starting from SQL Server 2012 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(100));
INSERT @tbl (tokens) VALUES
('123/123/123456789/12'),
('123/123/9876543210/12');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '/';

SELECT t.* 
   , ThirdToken = c.value('(/root/r[position() eq 3]/text())[1]', 'VARCHAR(100)')
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
      REPLACE(tokens, @separator, ']]></r><r><![CDATA[')   
      ']]></r></root>' AS XML)) AS t1(c);

Output

ID	tokens	ThirdToken
1	123/123/123456789/12	123456789
2	123/123/9876543210/12	9876543210

CodePudding user response：

With 4 parts to your data as shown you can abuse the parsename function:

declare @string varchar(50) = '123/123/123456789/12';

select ParseName(Replace(@string,'/','.'),2);

CodePudding user response：

Since you are on 2016, consider the following

Example

Declare @YourTable table (ID int,SomeCol varchar(50))
Insert Into @YourTable values 
 (1,'123/123/123456789/12')

Select A.ID
      ,Pos3 = JSON_VALUE(JS,'$[2]')
 From  @YourTable A
 Cross Apply (values ('["' replace(SomeCol,'/','","') '"]') ) B(JS)

Results

ID  Pos3
1   123456789

If you only need the single value, there is no need for the CROSS APPLY

Select A.ID
      ,Pos3 = JSON_VALUE('["' replace(SomeCol,'/','","') '"]','$[2]')
 From  @YourTable A