Using String_Split to define more then one condition for a split

Question

This is a syntax question really s
I was able to write a query that took a column and split it based on the number of ' ; ' in the string

1. not all the rows in the column have ';' some have spaces
   so I need to either limit the query to work only when there is ';' in the string
   or better off to add another condition to the cross apply to include space.
   Any idea how ?
2. Sometimes the word AKA appear in name, in this case i need to record to be bypass
3. after splitting the name i need to remove the original name from the name column.
4. Thank You for all

    DECLARE @tbl AS TABLE (id int identity(1,1) PRIMARY KEY,Name nvarchar(max))
   
    INSERT @tbl (Name)
    VALUES ('BAIL HALEN M; LYNN DRAGON L; LYNN WAYLON Z'),
    ('BRIDSHAW ERWIN DEIUL; BRIDSHAW RON CARL'),
    ('CROOK BATTY M WALSH DELLA J');
   
   
   Select ID, Name , ISNULL([Name1],'') as [Name1],ISNULL([Name2],'') as [Name2],
   ISNULL([Name3],'') as [Name3],ISNULL([Name4],'') as [Name4]  from (
   SELECT id, Name, 'Name' + Cast( ROW_NUMBER() Over(Partition by name order by id) As varchar) as col,
   split.value As SplitValue FROM @tbl
   Cross Apply string_split(Name,';')  as split
    where Name is not null 
    ) as tbl
    Pivot (max(SplitValue) for col in ([Name1],[Name2],[Name3],[Name4])) as pvt
   

Answer 1

Hi @Debilon ,

Please try the following solution.
There is an assumption that each name has 3 words/parts.

SQL

-- DDL and sample data population, start  
DECLARE @tbl AS TABLE (id int identity(1,1) PRIMARY KEY,tokens nvarchar(max));  
INSERT @tbl (tokens) VALUES   
('BAIL HALEN M; LYNN DRAGON L; LYNN WAYLON Z'),  
('BRIDSHAW ERWIN DEIUL; BRIDSHAW RON CARL'),  
('CROOK BATTY M WALSH DELLA J');  
-- DDL and sample data population, end  
  
DECLARE @separator CHAR(1) = SPACE(1);  
  
SELECT *  
, c.query('data(/root/r[position() le 3])').value('.','VARCHAR(100)') AS Col1  
, c.query('data(/root/r[position() = (4,5,6)])').value('.','VARCHAR(100)') AS Col2  
, c.query('data(/root/r[position() ge 7])').value('.','VARCHAR(100)') AS Col3  
FROM @tbl AS t  
CROSS APPLY (SELECT TRY_CAST('<root><r>' +   
      REPLACE(REPLACE(tokens,';',''), @separator, '</r><r>') +   
      '</r></root>' AS XML)) AS t1(c)  
WHERE tokens NOT LIKE '%AKA%';  

Output

+----+--------------------------------------------+----------------------+-------------------+---------------+  
| id |                   tokens                   |         Col1         |       Col2        |     Col3      |  
+----+--------------------------------------------+----------------------+-------------------+---------------+  
|  1 | BAIL HALEN M; LYNN DRAGON L; LYNN WAYLON Z | BAIL HALEN M         | LYNN DRAGON L     | LYNN WAYLON Z |  
|  2 | BRIDSHAW ERWIN DEIUL; BRIDSHAW RON CARL    | BRIDSHAW ERWIN DEIUL | BRIDSHAW RON CARL |               |  
|  3 | CROOK BATTY M WALSH DELLA J                | CROOK BATTY M        | WALSH DELLA J     |               |  
+----+--------------------------------------------+----------------------+-------------------+---------------+

Answer 2

Hi @Debilon

limit the query to work only when there is ';' in the string

You can add a filter condition in where clause:

WHERE CHARINDEX(';',Name)>0  

better off to add another condition to the cross apply to include space

Considering that there will be spaces in the middle of normal names, it is unwise to use string_split to split different names according to spaces.
Or suppose, as Yitzhak posts , that all names have three parts ,then you can refer to his answer.

Sometimes the word AKA appear in name, in this case i need to record to be bypass

Similar method as above: add a filter

WHERE Name NOT LIKE '%AKA%';  

after splitting the name i need to remove the original name from the name column.

You need an 'add columns' statement along with an update statement.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".