Extract strings till the second delim SQL

Question

I wanted to extract all the details till the second /(forward slash)from my table in SQL Server. Any ideas?

website
AA.AA/AB/123
www.google.com/en/abcd/
yahoo.com/us/dev
gmail.com

ouput
website
AA.AA/AB
www.google.com/en
yahoo.com/us
gmail.com

Answer 1

Perhaps this will suit your needs:

DECLARE @Table TABLE (Col1 NVARCHAR(100))
INSERT @Table VALUES
('website'),
('AA.AA/AB/123'),
('www.google.com/en/abcd/'),
('yahoo.com/us/dev'),
('gmail.com')



SELECT 
COALESCE(
    NULLIF(
        SUBSTRING(Col1,1,CHARINDEX('/',Col1,CHARINDEX('/',Col1)+1))
        ,'')
   ,Col1    
    ) AS Col1
FROM @Table

Answer 2

If you are using SQL Server 2017 or 2019, you can use STRING_AGG() to reassemble the output from STRING_SPLIT():

SELECT STRING_AGG(x.value, '/')
FROM dbo.table_name CROSS APPLY 
(
  SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
  FROM STRING_SPLIT(Col1, '/') AS ss
) AS x(value, rn)
WHERE x.rn <= 2
GROUP BY Col1;

You might say:

"But Aaron, the output of STRING_SPLIT() isn't guaranteed to be in order; in fact the documentation warns about that."

This is true; the documentation does say that. But in current versions the output is extremely unlikely to be in anything but left-to-right order. I still suggest you be wary of relying on this, since it could break at any time (I warn about this in more detail here).

If you are on an older version, or don't trust it, you can use a table-valued function that preserves the order of the input string, for example from this answer:

CREATE FUNCTION [dbo].[SplitString]
(
    @List NVARCHAR(MAX),
    @Delim VARCHAR(255)
)
RETURNS TABLE
AS
    RETURN ( SELECT [Value], idx = RANK() OVER (ORDER BY n) FROM 
      ( 
        SELECT n = Number, 
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
          FROM sys.all_objects) AS x
          WHERE Number <= LEN(@List)
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
      ) AS y
    );

With that function in place, you can then do the following, and now feel safer about relying on order (at the cost of a more expensive query):

;WITH src AS 
(
  SELECT Col1, idx, Value  
  FROM dbo.table_name CROSS APPLY dbo.SplitString(Col1, '/')
)
SELECT STUFF((SELECT '/' + Value
  FROM src
  WHERE src.idx <= 2 AND Col1 = t.Col1
  ORDER BY idx
  FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, '')   
FROM dbo.table_name AS t
GROUP BY Col1;

Answer 3

I find cross apply handy for these situations

select case when str like '%/%' then left(str, i2-1) else str end as str
from t
cross apply (select charindex( '/', str ) as i1) t2           --position of first slash
cross apply (select charindex( '/', str, (i1 + 1)) as i2 ) t3 --position of second slash

Answer 4

Below is the simple query you can try. In the below query please replace 'colName' with your column name and Table_1 with your table name.

SELECT  LEFT([colName], charindex('/', [colName], charindex('/', [colName])+1)-1) AS [AfterSecondPipe]
FROM    [Table_1]