How do I return data from a SQL String_split function into my table

Question

Using the newest SQL update and String Split Function how do I insert the split data back into my table?

select be.*, SS.value
from b2bemployee be
outer apply string_split(firstname, ' ') AS SS
where id=2319

Using that I split my FirstName column into two values and the data is returned on two separate rows. I am trying to insert this data back into my table as updatedfirstname (before the space) and updatedlastname (after the space)

Use `SUBSTRING()` instead for this. http://stackoverflow.com/questions/10921400/t-sql-substring-separating-first-and-last-name — S3S, Jun 28 '16 at 18:00

score 3 · Answer 1 · answered Jun 28 '16 at 18:02

3

The Split functions can be slow, especially with larger tables.

Consider ParseName(), for example

Declare @String varchar(25) = 'John Smith 999-11-1234'
Select FirstName=PARSENAME(Replace(@String,' ','.'),3)
      ,LastName =PARSENAME(Replace(@String,' ','.'),2)
      ,SSN      =PARSENAME(Replace(@String,' ','.'),1)

--Returns--
FirstName   LastName    SSN
John        Smith       999-11-1234

So, in your case, and assuming your delimiter is a space AND your field looks like "Jame Smith"

Select FirstName=PARSENAME(Replace(YourFieldName,' ','.'),2)
      ,LastName =PARSENAME(Replace(YourFieldName,' ','.'),1)

answered Jun 28 '16 at 18:02

John Cappelletti

79,615
7
44
66

Just be careful with this if you are going to parse out more than 4 delimiters. – dfundako Jun 28 '16 at 19:52
@dfundako Agreed, I've made assumptions in the past and I was wrong on a cosmic scale. This assumption I could live with – John Cappelletti Jun 28 '16 at 20:04

score 2 · Answer 2 · answered Jun 28 '16 at 17:54

I think this will work instead:

Create Table #Test (Value Varchar(8000), LeftPart Varchar(8000), RightPart Varchar(8000))
Insert #Test Values ('Joe Cool', '', '')
Insert #Test Values ('John Doe', '', '')


Update #Test Set LeftPart = Substring(Value, 1, Charindex(' ', Value) - 1), RightPart = Substring(Value, Charindex(' ', Value) + 1, 8000) From #Test
Select * From #Test

Also note that unless you plan to drop the full field after the split, this is a good case for computed columns.

gofr1 · Answer 3 · 2016-06-28T19:49:00.447

If you want to use STRING_SPLIT (the ways with PARSENAME and SUBSTRING are more adequate for this situation) you need to do something like this:

;WITH cte AS (
SELECT  be.*,
        SS.value as FN, 
        ROW_NUMBER() OVER (PARTITION BY be.id ORDER BY (SELECT 1)) as RN
FROM b2bemployee be 
OUTER APPLY STRING_SPLIT(be.firstname, ' ') as SS
WHERE be.id=2319
)

SELECT  c1.*,
        c2.FN
FROM cte c1
LEFT JOIN cte c2
    ON c1.RN+1 = c2.RN
WHERE c1.RN = 1

I would suggest to use LEFT and RIGHT

SELECT  be.*,
        RIGHT(be.firstname,CHARINDEX(' ',be.firstname)) as updatedfirstname ,
        LEFT(be.firstname,LEN(be.firstname) - CHARINDEX(' ',be.firstname)-1) as updatedlastname 
FROM b2bemployee be 
WHERE be.id=2319

How do I return data from a SQL String_split function into my table

3 Answers3