How to perform Split and Stringbuilding in SQL?

Question

I'm a SQL acolyte, spending most of my time in Powershell. So for the purposes of this, I shall express myself thusly. Basically, I have a column of FullName, that contains FirstName LastName, and I want it restructured to LastName, Firstname in a SELECT query.

If I wasn't clear enough, in Powershell I would do this:

$string = "John Smith"
$split = $string.Split(' ')
$builder = "$($split[0]), $($split[1])"

How does one accomplish this in SQL?

What if someone's full name is `John Paul Van Der Veere`? Is it okay if it splits on the wrong space, since that's what would have happened in your Powershell script? — Adam V, Mar 16 '17 at 21:56
Yes, the column I'm dealing with is exclusively First Last without additional names. — user3066571, Mar 16 '17 at 22:18

DavidG · Accepted Answer · 2017-03-17T15:25:09.597

1

As your data is nice and consistent, you can use a combination of the CHARINDEX and SUBSTRING functions:

SELECT
    SUBSTRING(FullName, 0, CHARINDEX(' ', FullName)) AS FirstName,
    SUBSTRING(FullName, CHARINDEX(' ', FullName)+1, LEN(FullName)) AS LastName
FROM NamesTable

Now if you want to join them back together, just use some string concatentation:

SELECT
    SUBSTRING(FullName, CHARINDEX(' ', FullName)+1, LEN(FullName))
    + ', ' +
    SUBSTRING(FullName, 0, CHARINDEX(' ', FullName))
FROM NamesTable

edited Mar 17 '17 at 15:25

answered Mar 16 '17 at 22:32

DavidG

113,891
12
217
223

Would that not separate them into two columns? How would I combine them into one, separated by a comma and space? – user3066571 Mar 17 '17 at 15:23
1

Yes, but I thought it would be fairly obvious how to concatenate them together again - I've added the code for that. – DavidG Mar 17 '17 at 15:25

score 0 · Answer 2 · edited Mar 16 '17 at 22:19

0

Sql Server 2016. https://msdn.microsoft.com/en-us/library/mt684588.aspx

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'  

SELECT value  
FROM STRING_SPLIT(@tags, ',')  
WHERE RTRIM(value) <> '';

edited Mar 16 '17 at 22:19

DavidG

113,891
12
217
223

answered Mar 16 '17 at 22:16

Maxim Ilyukhin

81
1
5

In your DECLARE statement, is there a way to make that the content of a column I am querying? As opposed to doing a static assignment? – user3066571 Mar 16 '17 at 22:18
yes. SELECT value FROM STRING_SPLIT((select UserName from dbo.Users), '@') WHERE RTRIM(value) <> ''; – Maxim Ilyukhin Mar 16 '17 at 22:32

How to perform Split and Stringbuilding in SQL?

2 Answers2