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?

Answer 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