how to split the long text with separation of " - " (update as name, title, company in sql

Question

Hi i need the query for below sample text to split into Name, title, company with separation of " - "

Example:-

Full Text

Do Yun-Kim - Project Manager - Pioneer Windows Mfg. Corp.
Chen-Yi Li - Solutions Consultant - Worldpay
Linda Hager - Presales - Kronos
Ryan Asher
Steve Collins - RVP Sales - Enterprise
Bruce Peck - Corolla  North Carolina
Phillip Bartling - Managing Partner - Your Fantasy League Partners
Perry Tran - Data Analyst - MobilityWare
Katherine Tran - Principal Quality Assurance Engineer - Western
Wayne Peters - WW Sales - Microsoft
Asrith Inuganti - Account Relationship Manager - Shine.com
Seth Catalli - Regional Vice President - UiPath

Output :-

Name              title                    company
Do Yun-Kim        Project Manager          Pioneer Windows Mfg. Corp.
Bruce Peck        Corolla  North Carolina
Linda Hager       Presales                 Kronos

Microsoft SQL Server an Postgres are two very different products. Which one are you really using? — , Nov 30 '19 at 10:01
Does this answer your question? [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — Amira Bedhiafi, Nov 30 '19 at 10:04
What does it mean you want to query text? Is this text in a table? In one single cell (one column and one row)? Or in a column (one row per line)? Or not in a table yet at all? — Thorsten Kettner, Nov 30 '19 at 10:18
@perusjosh . . . I removed the inconsistent database tags. Please tag only with the database you are really using. — Gordon Linoff, Nov 30 '19 at 11:47

score 0 · Answer 1 · answered Nov 30 '19 at 10:04

0

With Postgres this can easily be done using split_part:

select full_text, 
       split_part(full_text, '-', 1) as name,
       split_part(full_text, '-', 2) as title,
       split_part(full_text, '-', 3) as company
from the_table;

answered Nov 30 '19 at 10:04

Thank you, i tried the above code, but am getting the following error, "Msg 195, Level 15, State 10, Line 16 'split_part' is not a recognized built-in function name." am using SQL 2014 version. can i get any suggestion to execute the query. – perus josh Dec 13 '19 at 09:22

score 0 · Answer 2 · answered Nov 30 '19 at 10:06

If you are using MS SQL You can use parsename like this:

Declare @t table (input nvarchar(500))

Insert into @t values ('Linda Hager-Presales-Kronos')

select  PARSENAME(replace(input,'-','.'),3) as 'Name',
        PARSENAME(replace(input,'-','.'),2) as 'Title',
        PARSENAME(replace(input,'-','.'),1) as 'Company'
 from @t

how to split the long text with separation of " - " (update as name, title, company in sql

2 Answers2