November 22, 2002 at 9:42 am
How would you split a name field into two parts a Last Name and a First Name. Is there any way to do this in SQL alone. The format in the name field is LASTNAME, FIRSTNAME. I can do this using Visual Basic by looking for a Comma with the INSTR command if it exists I can parse the last name and first name and then write it to a another table but I was wondering if there was a way to this just using SQL Server by setting up a CURSOR.
November 22, 2002 at 10:21 am
This is how I do it in SQL 7, in 2000 you can make a FNAME and LNAME function to make it easier based on the below. ANd just change 'LASTNAME, FIRSTNAME' to you column name.
SELECT
RIGHT('LASTNAME, FIRSTNAME', LEN('LASTNAME, FIRSTNAME') - CHARINDEX(' ',REVERSE('LASTNAME, FIRSTNAME'))) AS FNAME,
LEFT('LASTNAME, FIRSTNAME',CHARINDEX(',','LASTNAME, FIRSTNAME') - 1) AS LNAME
November 22, 2002 at 1:11 pm
DECLARE @LAST_FIRST VARCHAR(50)
SET @LAST_FIRST = 'BORRELLI, ANDY'
SELECT SUBSTRING(@LAST_FIRST,1,CHARINDEX(',',@LAST_FIRST)-1),
SUBSTRING(@LAST_FIRST,CHARINDEX(',',@LAST_FIRST)+1,LEN(@LAST_FIRST)),
@LAST_FIRST
November 22, 2002 at 1:13 pm
with aliased columns
DECLARE @LAST_FIRST VARCHAR(50)
SET @LAST_FIRST = 'BORRELLI, ANDY'
SELECT SUBSTRING(@LAST_FIRST,1,CHARINDEX(',',@LAST_FIRST)-1) LAST,
SUBSTRING(@LAST_FIRST,CHARINDEX(',',@LAST_FIRST)+1,LEN(@LAST_FIRST)) FIRST,
@LAST_FIRST
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply