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

Question

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!

Pawan Kumar · Accepted Answer · 2018-02-08 09:39:51Z

3

SOLUTION

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

OUTPUT

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

(1 row affected)

answered Feb 8, 2018 at 9:39

Pawan Kumar

2,02112 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sreenu131 · Accepted Answer · 2018-02-08 09:58:54Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related