sql query to find the second '/' in the column value and split into two columns

Question

Can I know how can I get only the data after the second '/'. It okay if they are broke into three columns but I have to use the data after the second delimiter.

Path:

/abc/US/123

/dd/IN/222

output:

US

IN

Answer 1

Here is an option which uses a bit of XML.

To be clear, we use ....value('/x[3]'... due to the leading /

Example

Declare @YourTable table (ID int,path varchar(100))
Insert Into @YourTable values
 (1,'/abc/US/123')
,(2,'/dd/IN/222')


Select ID
      ,SomeValue = Cast('<x>' + replace(path,'/','</x><x>')+'</x>' as xml).value('/x[3]','varchar(max)')
 From @YourTable

Returns

ID  SomeValue
1   US
2   IN