October 16, 2008 at 8:49 am
Hi all,
I have a table with a field called FullName that contains lastname, firstname and middle init. I run a query on this table selecting all fields and then I save the results as a csv file. When I save it, for some reason it automatically converts the format to be lastname in the first field and then first name and middle init. in the second field in the file. I want to have the output come out as first name in the first field, middle init. in the second field and lastname in the third field. I believe the split command does something similar to this. Is there a way to rearrange the format through either SQL or Excel to achieve this outcome?
Thank you,
Perry
October 16, 2008 at 8:55 am
You can do this in SQL ,
Post up some sample data and the query that you wrote and I am sure that some one will be able to help you
October 16, 2008 at 9:06 am
SELECT fullname
FROM users
Contents of fullname column
Col. A
Austin, Martin B
Brown, Steven A
Carter, Marvin E
Davis, Eric L
Francis, Mary G
Desired output is
Col. A Col. B. Col. C
Martin B Austin
Steven A Brown
Marvin E Carter
Eric L Davis
Mary G Francis
October 16, 2008 at 9:09 am
Firstly, having a name in your database called Fullname containing First MI and Last probably isn't the best idea from ta design standpoint as you probably also have columns with jusy the First, MI and Last in them. This creates duplication of data. If you don't have these other columns, my recommendation would be to opt for them instead of a Fullname column. It's always easier to put multiple columns together in display that try to pull them apart like you're attempting...
That said, the reason why you are getting the results you currently are is because you are outputting your data to a .csv or comma separated file. Your Fullname Column, if my assumption is correct has a comma between the lastname and the rest of the name.
That's why it gets broken up into 2 columns when you output it. Whatever you're viewing it in (Excel I'm assuming) is seeing the comma and splitting the column there.
As the previous poster said, we may be able to help you split this column using Tsql but we'll need some sample data to work with. Please refer to the following post as it will help us help you in the most expeditious fashion.http://qa.sqlservercentral.com/articles/Best+Practices/61537/
October 16, 2008 at 9:14 am
Looks like I was a bit late to the party and you've already posted some sample data. one question about it though. Do you allow people to have 2 first names in your application or more importantly people with spaces in their first name? For instance, I once knew a girl by the name of Mary Ann. Her entire first name was "Mary Ann" and she was somewhat insistent upon it. That will make your data a bit more difficult to parse...
October 16, 2008 at 9:20 am
No, two first names are not allowed. And I agree about how crazy it is with the entire full name in one column. I would love to change it along with a lot of other stuff but the table is part of the backend of a highly specialized, poorly developed proprietary application so unfortunately my hands are tied.
October 16, 2008 at 9:41 am
so something like the following may work for you I suppose?
CREATE TABLE #temp (
FullName VARCHAR(50))
INSERT INTO [#temp]
SELECT 'Austin, Martin B' UNION ALL
SELECT 'Brown, Steven A' UNION ALL
SELECT 'Carter, Marvin E' UNION ALL
SELECT 'Davis, Eric L' UNION ALL
SELECT 'Francis, Mary G'
SELECT Fullname,
LEFT([FullName],CHARINDEX(',',[Fullname])-1) AS LastName,
RIGHT([Fullname], 1) AS MI,
SUBSTRING(Fullname, CHARINDEX(',',[Fullname]) + 1, CHARINDEX(',',[Fullname],CHARINDEX(',',[Fullname]))) AS FirstName
FROM #temp
DROP TABLE [#temp]
October 16, 2008 at 9:43 am
If the data is consistent across all the names, then this code will be able to split the names, I tested it against the small sample provided and it works. Any deviations in the format will mean that the data will not be split easily.
declare @name varchar(100)
set @name = 'Francis, Mary G'
select ltrim(substring(@name,charindex(',',@name)+1,len(@name)-(charindex(',',@name)+1))) as FirstName,
right(@name,1) as MiddleInitial ,
left(@name,(charindex(',',@name)-1)) as LastName
Another way to approach this would be to enclose your strings in double quotes(") when you export the data, and specify this on the import. This would solve the problem of the comma causing you problems.
October 16, 2008 at 10:01 am
Thanks to you both for you responses. It appears that both resolutions would require me to manually type in each name within the script to reformat them. Is there a way to avoid having to do this? There are over 600 names in the column. I might as well just manually type them into the spreadsheet and skip the script. My ultimate goal is to get these names formatted correctly in a spreadsheet. I do not want to change the format within the database itself.
October 16, 2008 at 10:03 am
actually I had a typo in mine... What I meant to post was...
CREATE TABLE #temp (
FullName VARCHAR(50))
INSERT INTO [#temp]
SELECT 'Austin, Martin B' UNION ALL
SELECT 'Brown, Steven A' UNION ALL
SELECT 'Carter, Marvin E' UNION ALL
SELECT 'Davis, Eric L' UNION ALL
SELECT 'Francis, Mary G'
SELECT Fullname,
LEFT([FullName],CHARINDEX(',',[Fullname])-1) AS LastName,
RIGHT([Fullname], 1) AS MI,
SUBSTRING(Fullname, CHARINDEX(',',[Fullname]) + 1, CHARINDEX(' ',[Fullname],CHARINDEX(',',[Fullname])+2) - (CHARINDEX(',',[Fullname])+1) ) AS FirstName
FROM #temp
DROP TABLE [#temp]
Either mine or steve's methods will work and they performed the same on my test machine, YMMV however so test em both and see which one gives you the results you're looking for.
-Luke.
October 16, 2008 at 10:13 am
perrydwill (10/16/2008)
Thanks to you both for you responses. It appears that both resolutions would require me to manually type in each name within the script to reformat them. Is there a way to avoid having to do this? There are over 600 names in the column. I might as well just manually type them into the spreadsheet and skip the script. My ultimate goal is to get these names formatted correctly in a spreadsheet. I do not want to change the format within the database itself.
Neither of the solutions provided will require you to type out the 600 names, you just need to modify the script to select the full-name from your table.
eg.
select rtrim(ltrim(substring(FULLNAME,charindex(',',FULLNAME)+1,len(FULLNAME)-(charindex(',',FULLNAME)+1)))) as FirstName,
right(FULLNAME,1) as MiddleInitial ,
left(FULLNAME,(charindex(',',FULLNAME)-1)) as LastName
FROM YourTableName
October 16, 2008 at 10:24 am
Well I tried to run this portion of your script against the existing table:
SELECT Fullname,
LEFT([FullName],CHARINDEX(',',[Fullname])-1) AS LastName,
RIGHT([Fullname], 1) AS MI,
SUBSTRING(Fullname, CHARINDEX(',',[Fullname]) + 1, CHARINDEX(' ',[Fullname],CHARINDEX(',',[Fullname])+2) - (CHARINDEX(',',[Fullname])+1) ) AS FirstName
FROM user
But I got "Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function." using either script.
October 16, 2008 at 10:32 am
You've got a record in there that doesn't contain a ',' after the last name.
that would be the if the data's not the same for each record it becomes increasingly difficult to do bit that Steve spoke of earlier...
-Luke.
October 16, 2008 at 10:42 am
I ahve to agree with Luke's comments, it is apparent that all your names do not follow the same format. You could try
SELECT Fullname,
LEFT([FullName],CHARINDEX(',',[Fullname])-1) AS LastName,
RIGHT([Fullname], 1) AS MI,
SUBSTRING(Fullname, CHARINDEX(',',[Fullname]) + 1, CHARINDEX(' ',[Fullname],CHARINDEX(',',[Fullname])+2) - (CHARINDEX(',',[Fullname])+1) ) AS FirstName
FROM user
WHERE CHARINDEX(',',FullName) > 0
This will be able to convert the names that contain a comma, you may have to do the other ones manualy.
October 16, 2008 at 10:45 am
Oh crap. After looking at the table more closely the format isn't consistent. I have some that have their whole middle name instead of initial, some that have jr, some that have four whole names listed... No easy way around this is it?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply