Splitting row in column

Question

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

I removed the incompatible database tags. Please tag with the database you are really using. — Gordon Linoff, Apr 04 '19 at 19:14
Is there only ever 2 languages that need to be split or is it n number of languages? — derek.wolfe, Apr 04 '19 at 19:19
Where does the "tennis" come in the french version for the first three? Where did the " ." at the end and the " " at the beginning of the first row go? The third also seems to lose spaces... — sticky bit, Apr 04 '19 at 19:22
@drakin8564 its only these 2 language and i am selecting it by site id as only this site has these 2 language. — Akki, Apr 04 '19 at 19:26
@stickybit this is messed up data so we used ' / ' delimiter to split most of it and '.' might be typo — Akki, Apr 04 '19 at 19:27
This will be very RDBMS specific. Can you please share the platform you are on? I'm also not following how this is split. How do we know that the just the term "Drop-In" is to be replaced by just the terms "La Ligue de"? Why is "Tennis" retained here? It's not at all clear what the rules for applying these replacements are and it's not a simple delimited/string-split scenario. — JNevill, Apr 04 '19 at 19:32
Like... is the rule here that if we encounter a `"/"` character that we *REMOVE* the next word (non-space characters until a space is encountered) with EVERYTHING before the `"/"` character to construct the French string? Or, in other words, is the rule here to remove the `/` as well as any single following word? — JNevill, Apr 04 '19 at 19:36
Possible duplicate of [SQL split column data](https://stackoverflow.com/questions/14588339/sql-split-column-data) — Zack, Apr 04 '19 at 19:39
@JNevill I think I might not be clear with my question here so what I am trying to do is to split the notes column into 2 separate column where we have ' / ' delimiter in it and if there is no ' / ' delimiter the notes should go to English column by default. I don't know if it is possible in SQL but when I did it in Bigquerry it worked here it is https://stackoverflow.com/questions/55284867/splitting-row-in-2-columns — Akki, Apr 04 '19 at 19:54
I think it would be very helpful if you changed your desired results above to reflect what you are wanting to do. This seems like a pretty straight forward strtok/split_string question if that's the case. — JNevill, Apr 04 '19 at 19:54
that is the desired result what I am looking for. I want notes to be splited in to columns english and french where ' / ' delimiter is available. — Akki, Apr 04 '19 at 20:03

derek.wolfe · Accepted Answer · 2019-04-04T20:14:22.167

2

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}

edited Apr 04 '19 at 20:14

answered Apr 04 '19 at 19:37

derek.wolfe

1,086
6
11

This doesn't output OP's desired results. Note that the string `La ligue de / Drop-In Tennis` becomes `La ligue de tennis`. Note that it doesn't become `La Ligue de` as your answer would output. – JNevill Apr 04 '19 at 19:39
I did not notice that. I have edited the answer to account for that. – derek.wolfe Apr 04 '19 at 19:45
@drakin8564 your last query was helpful as it was giving me almost the result that I was looking for but it only had / of french column at the end which i don't need. – Akki Apr 04 '19 at 20:09
Easy fix. I subtracted 1 from the position that the LEFT() function goes to, which will cut off that character. – derek.wolfe Apr 04 '19 at 20:15

score -1 · Answer 2 · answered Apr 04 '19 at 19:29

-1

Working example for postgres:

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

answered Apr 04 '19 at 19:29

begin29

149
6

This doesn't output OP's desired results. Note that the string `La ligue de / Drop-In Tennis` becomes `La ligue de tennis`. Note that it doesn't become `La Ligue de` as your answer would output. – JNevill Apr 04 '19 at 19:39

Splitting row in column

2 Answers2