Print Substring before and after slash '/' from a string

Question

I've a task to print substring from a string based on the occurence of slash '/' position in a string .Below i'm trying to show you one sample example how i want to get .

Declare @My_string = 'abc/def gh /ijk l/m/no p/qr

From the above string i want to print the substring value based on the position of slash occourence. For Example: Sometimes i was asked to print the substring after the 2nd occourence of slah then i've to display ijk l and Sometimes i was asked to print the substring after the 3rd occourence then m should display if it is 1st then def gh vice versa .There is no specific occourence position it may vary based on need .How can i achieve it .Any help will be very thankful

Note: I want to achieve without using function .i know that it can be done by using split function .Is that possible to get without using it

Possible duplicate of [Split function equivalent in T-SQL?](https://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) — Jeroen Mostert, Sep 07 '17 at 13:11
Which MS SQL version are you targeting? Azure SQL and SQL Server 2016 have STRING_SPLIT? For older versions, there are many answers. — Daniel Tsvetkov, Sep 07 '17 at 13:11
Split the string into a table where one column is the index and the other is the text (so in your example `abc` will have index 0, `def gh` will have index 1 and so on. After that it's a plain and simple select statement. — Zohar Peled, Sep 07 '17 at 13:11
@JeroenMostert i want to achieve without function .i know that it can be done by using spli function .is that possible to get without using it — Mahesh.K, Sep 07 '17 at 13:12
Did you check the answers? Several do not declare functions. — Jeroen Mostert, Sep 07 '17 at 13:13
@ZoharPeled Do we have any reference links for that approach .It will be helpfull for me if you share any links . — Mahesh.K, Sep 07 '17 at 13:25
The best performing way is to import the function with CLR https://sqlperformance.com/2012/07/t-sql-queries/split-strings — Jodrell, Sep 07 '17 at 13:41

score 1 · Answer 1 · answered Sep 07 '17 at 13:16

1

A simple way to get the 3rd position via XML

Example

Declare @My_string varchar(max)= 'abc/def gh /ijk l/m/no p/qr'

Select Pos3 = convert(xml,'<x>'+replace(@My_String,'/','</x><x>')+'</x>').value('/x[3]','varchar(100)')

Returns

Pos3
ijk l

answered Sep 07 '17 at 13:16

John Cappelletti

79,615
7
44
66

Here the third postion substring should be `m` beacuse `m` is the substring after the third occurence of slash . And moreover can i pass position value using a variable or somehow .And also third position is not my fixed position to display . – Mahesh.K Sep 07 '17 at 13:22
@Mahesh.K With this approach, the first position (or string) would be abc. Then perhaps a table-valued function to split the string – John Cappelletti Sep 07 '17 at 13:31

Zohar Peled · Accepted Answer · 2017-09-07T13:36:15.550

One way to do it without using a function is to use a recursive cte.
This is not the way I would recommend splitting a string but since you insist on not using a function it's a reasonable alternative.
If you change your mind about splitting string function, you should read Aaron Bertrand's Split strings the right way – or the next best way and choose a split string function that would be easy to modify to return the item number as well.

Sample data:

DECLARE @My_string varchar(100) = 'abc/def gh /ijk l/m/no p/qr',
        @SlashIndex int = 3

The CTE:

;WITH CTE AS
(
    SELECT  LEFT(@My_String, CHARINDEX('/', @My_String)-1) As Value,
            0 As SlashIndex,
            RIGHT(@My_String, LEN(@My_String) - CHARINDEX('/', @My_String)) As String
    WHERE CHARINDEX('/', @My_String) > 0
    OR LEN (@My_String) > 0

    UNION ALL 

    SELECT  LEFT(String, CASE WHEN CHARINDEX('/', String) > 0 THEN CHARINDEX('/', String) ELSE LEN(String) +1 END -1),
            SlashIndex + 1,
            RIGHT(String, LEN(String) -  CASE WHEN CHARINDEX('/', String) > 0 THEN CHARINDEX('/', String) ELSE LEN(String) END)
    FROM CTE
    WHERE CHARINDEX('/', String) > 0 
    OR LEN(String) > 0
)

The Query:

SELECT Value
FROM CTE
WHERE SlashIndex = @SlashIndex

Result: m

You can see a live demo on rextester.

Great !.it works but ,your code is little complex to understand .can't we make it little easy .one thing i've understood is you are doing using recursive cte . — Mahesh.K, Sep 07 '17 at 13:36
Then check out Aaron's article I've linked to and adapt one of the functions he lists there. some of them are much simpler. — Zohar Peled, Sep 07 '17 at 13:39
okay @Zohar Peled .. Thanks .one more thing if want to stick to a specific position like 1 or 2 or 3rd occurence will it be a good approach . — Mahesh.K, Sep 07 '17 at 13:41

score 1 · Answer 3 · answered Sep 07 '17 at 13:35

created below function which will take 2 input first - your position and second will be your string and return the required output -

 create  function dbo.GetSubsting ( @StatPoint int , @Input_string varchar(1000) )
 returns varchar(1000)
 as 
 begin 
 Declare @len int , @idx int =1 , @section int = 1 , @output varchar(1000) = ''  
 DECLARE  @tab table   ( idx int identity(1,1 ), Val char(1) , section int )

 select @len = len(@Input_string)

 while @idx <= @len
 begin
 insert into @tab
 select substring( @Input_string , @idx ,1)  , @section
 if substring( @Input_string , @idx ,1) = '/'
 set @section = @section + 1
 set @idx = @idx + 1 
 end 

 select @output = @output + Val from @tab where section = @StatPoint 

 select @output = replace(@output , '/' , '')

 RETURN  @output
 end 
 go

 select dbo.GetSubsting(3, 'abc/def gh /ijk l/m/no p/qr')
 --OUTPUT 
 -- ijk l

Print Substring before and after slash '/' from a string

3 Answers3