How to extract the last name from a full name field 2

[cmmrfrds]

In SQL. I have a field with first name and last name. I would like to reverse the order and put last name first. Also, add another name in front of the last name which is in another field. The result would look Like this.
othername- lastname firstname

Jones- Smith Sam
Jones- Johnson Arnold
Sampson- Revere Paul

I would like to print in a result set and ultimately update another field, but I can handle that once I get the field formatted.

 

[jhall156]

IF You're using SQL Server 2000
You could try...

DECLARE @OtherNames table (OtherName varchar(50))
INSERT @OtherNames
SELECT NameToPrefix + '-' +
RTRIM(SUBSTRING(FirstAndLast, CHARINDEX(' ', FirstAndLast) + 1, 50)) +
' ' +
LEFT(FirstAndLast, CHARINDEX(' ', FirstAndLast) - 1)
FROM tbl_Name

SELECT * FROM @OtherNames

Otherwise you can use the same concept but with a temp table

 

[jhall156]

I should probably mention that this will only work on FirstName<space>LastName configuration and if the FirstAndLast allows nulls you would need to check for that as well. So 'Yves St. Laurent' et al would not be parsed correctly. If those kinds of values are possible you need to have a more complicated system. I recently created some data &quot;clump&quot; analysis code so I know what you're up against.

 

[cmmrfrds]

Thank you. I was hoping there was a way to work from right to left. I know the last name will always be to the right and the first space prior to the last name would be the delimiter. The first name part has middle names and initials so it would be difficult to work from left to right and find the space in front of the last name. I would be happy with last name and first name plus any middle name or initials, but how do you work backwards from the last name to the space delimiter because I don't know how many spaces are between first name, middle initial, middle name, etc.. If I could just pull off the last string on the full name field that would work for me. The key would be to find the space in front of the last name working backwards. Is this possible??

 

[jhall156]

I don't know how you would encompass that within a select statement but you could try this.
I use this function to find all the non-character delimited clumps in the text of a message so that I can step through them one at a time. You could do the same thing starting at the last clump. It's probably not the best way but should work.

Here is the parsing function


CREATE FUNCTION dbo.fn_GetMailDataClump (@Message varchar(8000),
@StartPosition int)
RETURNS @Clump table (Clump varchar(256), NextStart int) AS
BEGIN
DECLARE @ClumpText varchar(256),
@StartClump int,
@ClumpLength int,
@Counter int,
@MsgLength int

SELECT @Message = RTRIM(@Message),
@MsgLength = LEN(@Message)
/* Find Beginning of Clump */
SET @Counter = 0
WHILE (ASCII(SUBSTRING(@Message, @StartPosition + @Counter,1)) <= 32 OR
ASCII(SUBSTRING(@Message, @StartPosition + @Counter,1)) >= 126) AND
@Counter < @MsgLength
BEGIN
SET @Counter = @Counter + 1
END
SET @StartClump = @StartPosition + @Counter
/* Find End of Clump */
SET @Counter = 0
WHILE (ASCII(SUBSTRING(@Message, @StartClump + @Counter,1)) >32 AND
ASCII(SUBSTRING(@Message, @StartClump + @Counter,1)) < 126)
BEGIN
SET @Counter = @Counter + 1
END
SET @ClumpLength = @Counter
/* Calc Clump */
INSERT @Clump
SELECT SUBSTRING(@Message, @StartClump, @ClumpLength),
@StartClump + @ClumpLength + 1
RETURN
END



And here is how it is called

DECLARE @StartPosition int,
@ClumpText varchar(256),
@MessageLength int

DECLARE @DataClumps table (ClumpText varchar(256),
NextStart int)

SELECT @StartPosition = 1,
@MessageLength = LEN(RTRIM(@Message))
WHILE @StartPosition < @MessageLength
BEGIN
SELECT @ClumpText = LTRIM(RTRIM(a.Clump)),
@StartPosition = a.NextStart
FROM fn_GetMailDataClump(@Message, @StartPosition) a
INSERT @DataClumps
VALUES (@ClumpText,
@StartPosition)
END
DECLARE DataClumps CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT ClumpText FROM @DataClumps
OPEN DataClumps
FETCH LAST FROM DataClumps INTO @ClumpText

etc.

@Message would instead be your field.

It's not really designed for what you have in mind but in a pinch it should work.

 

[tlbroadbent]

Here is a select statement that works in SQL 7 and 2000.

Select
OtherName + '- ' +
reverse(left(reverse(rtrim(FullName)), charindex(' ',reverse(FullName))-1)) + ' ' +
reverse(right(reverse(rtrim(FullName)), len(FullName)-charindex(' ',reverse(FullName)))) As NewName
From TableName Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cmmrfrds]

Thanks Terry, I like that, it is a nice compact routine. I was thinking about the reverse, but I had already used a cursor to loop through the records and broke it down into a few steps and got the job done.

 

[cmmrfrds]

Terry, the reverse works perfectly to display the result but when I inserted it into an update statement I get an error. It says the value in the fullname field cannot be made an int. My syntax must be wrong, can you see where the problem is?

Update name
Set NickName1 =
(Select
c.attorney_temp + '- ' +
reverse(left(reverse(rtrim(FullName)), charindex(' ',reverse(FullName))-1))
+ ' ' +
reverse(right(reverse(rtrim(FullName)), len(FullName)-charindex(' ',reverse(FullName))))
From name as n
inner join customc as c on c.recordidq = n.recordid
and name.recordid = n.recordid )
From name
where FullName > 0

 

[jhall156]

I think you mean LEN(FullName) in the last line.