sql server - SQL Newbie: Import First Name Only from Full Name Field to Report Builder (Data Source Query)

Question

I'm sure this is probably a very simple question but I'm new to SQL and I can't find anything on the forum that addresses exactly what I'm trying to accomplish, so bear with me.

I'm working in SQL Server Report Builder. I'm setting up my Data Source and Query. The table I'm querying only has a "Full Name" field, but, on this report, I need to split it up into "First Name" and "Last Name". I'd like to do all of this directly from the Dataset->Query section, if possible. The "Full Name" field is in this format: SMITH, MARK ALEXANDER

Some people have more than one first name, so, in the example above "Mark Alexander" can become "First Name".

I've been able to get this working for the Last Name using PARSENAME as so:

SELECT DISTINCT [Zip Code]
,PARSENAME(REPLACE([Full Name], ',', '.'), 2) AS [LastName]
FROM [SomeDB].[dbo].[T_MAIN_LIVE]

...but I have not been able to get it working correctly for the First Name. I've also tried using the RIGHT command but had similar issues. It either cut off part of the first name, ignored "secondary" first names, or printed the first name with a preceding space that I can't seem to get rid of. For example, the closest I can get is with this:

SELECT DISTINCT [Zip Code]
,PARSENAME(REPLACE([Full Name], ',', '.'), 1) AS [FirstName]
FROM [SomeDB].[dbo].[T_MAIN_LIVE]

...but I'm left with a preceding space. Maybe I just need to eliminate the preceding space after the fact? If so, how should I go about that?

Again, I'm sure this is a simple fix for you SQL experts so I apologize for my ignorance.

Thanks in advance, Cameron

Answer 1

You can try this. It worked for me.

SELECT name = 'smith, mark alexander'
INTO #name

SELECT concat(SUBSTRING(name, CHARINDEX(',', name, 1) + 1, 20), ' ', SUBSTRING(name, 1, CHARINDEX(',', name, 1) - 1))
FROM #name

EDIT: This assumes there is a comma separating the last from the first name in all instances.