CODEWITHSUNDEEP
sql
Akki
Akki

Reputation: 59

Splitting row in column

Trying to get data split by language where '/' delimiter is present It is sorted by site id so only id 412 has the french in it. was trying to use string_split() but it will split by row but i wan it by column Example is below

results that i want to be converted from 

siteid  notes                                           
412     Le cardio-/ Cardio Tennis      
412     Le cardio-/Cardio Tennis         
412     La ligue de / Drop-In Tennis    
411     An extended duration                           
411     Increase flexibility

Result I want to be

siteid  notes                           French                  english
412     Le cardio-/ Cardio Tennis      Le cardio-tennis        Cardio Tennis 
412     Le cardio-/Cardio Tennis        Le cardio-tennis        Cardio Tennis 
412     La ligue de / Drop-In Tennis    La ligue de tennis      Drop-In Tennis 
411     An extended duration            null                    An extended duration 
411     Increase flexibility            Null                    Increase flexibility

Upvotes: 0

Views: 68

Answers (2)

derek.wolfe
derek.wolfe

Reputation: 1116

Since you are using string_split() in your question i am assuming this is SQL Server, if no, ignore this answer.

I believe this would work:

SELECT siteid,
       notes,
       CASE WHEN CHARINDEX('/', notes) > 0
            THEN CONCAT(LEFT(notes, CHARINDEX('/', notes)-1),
                 RIGHT(notes,CHARINDEX(' ',REVERSE(notes))))
            ELSE ''
        END AS french,
       CASE WHEN CHARINDEX('/', notes) > 0
            THEN RIGHT(notes, LEN(notes) - CHARINDEX('/', notes))
            ELSE notes
        END AS english
  FROM {your_table}

Upvotes: 2

begin29
begin29

Reputation: 149

Working example for postgres:

Select split_part(name, '/', 1) AS Spanish, 
       split_part(name, '/', 2) as English
from translations

Upvotes: -1

Related Questions