Dilyana Flower
Dilyana Flower

Reputation: 125

How to split a string after specific character in SQL Server?

I have followed problem: I have one column, which contains a lot of strings. Something like this:

"Shop/Kaufprozess/Zahlungsart u. Zustellung"

The first two words are always different. This could be for example

"HTML-Title/Kaufprozess/Zahlungsart u. Zustellung" 

or

"Info/Template/Zahlungsart u. Zustellung“  
"tpl_landingpage/Kaufprozess/Zahlungsart u. Zustellung“ etc.

From this string, I want to have only first two wards! Example: Original text:

„Shop/Kaufprozess/Zahlungsart u. Zustellung“

I want to keep: Shop/Kaufprozess

How can I do this?

Thank you!

Upvotes: 1

Views: 834

Answers (2)

Sreenu131
Sreenu131

Reputation: 2516

Try this code

DECLARE @DataGet TABLE (id INT IDENTITY,String varchar(300))

INSERT INTO @DataGet
SELECT 'Shop/Kaufprozess/Zahlungsart u. Zustellung' UNION ALL
SELECT 'Info/Template/Zahlungsart u. Zustellung'    UNION ALL
SELECT 'tpl_landingpage/Kaufprozess/Zahlungsart u. Zustellung'


;WITH cte 
     AS (SELECT 1                      AS Seq, 
                id, 
                CHARINDEX('/', string) AS Pos, 
                string 
         FROM   @DataGet 
         UNION ALL 
         SELECT seq + 1, 
                id, 
                CHARINDEX('/', string, pos + 1) AS Pos, 
                string 
         FROM   cte 
         WHERE  pos > 0 
                AND seq <= 2) 
SELECT string,SUBSTRING(string, 0, pos)AS ReqString 
FROM   (SELECT *, 
               ROW_NUMBER () 
                 OVER( 
                   partition BY id 
                   ORDER BY seq ) AS Dup 
        FROM   cte 
        WHERE  pos > 0)dt 
WHERE  dup = 2 
ORDER  BY dt.id, 
          dt.dup ASC 

Result

String                                                  ReqString
-----------------------------------------------------------------------------------
Shop/Kaufprozess/Zahlungsart u. Zustellung              Shop/Kaufprozess
Info/Template/Zahlungsart u. Zustellung                 Info/Template
tpl_landingpage/Kaufprozess/Zahlungsart u. Zustellung   tpl_landingpage/Kaufprozess

Upvotes: 0

Pawan Kumar
Pawan Kumar

Reputation: 2021

SOLUTION

DECLARE @ AS VARCHAR(MAX) = 'Shop/Kaufprozess/Zahlungsart u. Zustellung'
SELECT SUBSTRING (@,1, CHARINDEX('/',@, CHARINDEX('/',@,0)+1) - 1 )

OUTPUT

    --------------------
    Shop/Kaufprozess

(1 row affected)

Upvotes: 3

Related Questions