CODEWITHSUNDEEP
sql-serversubstringcharactersql-server-2016
dnk
dnk

Reputation: 17

SQL obtain values between second and third forward slash

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

Upvotes: 0

Views: 280

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81970

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

Upvotes: 2

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

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

Upvotes: 3

Stu
Stu

Reputation: 32609

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);

Upvotes: 3

Related Questions