SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Splitting up LNAME, FNAME
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cstokes91
Yak Posting Veteran

USA
60 Posts

Posted - 03/19/2013 :  11:38:47  Show Profile  Reply with Quote
Hey,

I know this is a simple thing to do and can think of ways to do it but I am looking at the best way to do it...

The format of the data is column named 'Caller' that has the data stored in LNAME,FNAME. Right now, the script for getting the LNAME pulls in the comma too and I can run an update statement to remove it but I am trying to run correct the first time.

Here is my script for LNAME...


SELECT CALL#_CAD, caller, CAST(
			(
			substring
				(
				CALLER
				,1
				,CHARINDEX(',',CALLER) --I tried doing -1 here but it errored.
				)
			)
			AS VARCHAR(20)) AS LNAME	
FROM DATA.DBO.CDCALL


And here is my code for FNAME (This works perfectly as needed)


 SELECT CALL#_CAD, caller, CAST(
			(
			substring
				(
				CALLER
				,CHARINDEX(',',CALLER)+1
				,len(caller)
				)
			)
			AS VARCHAR(20)) AS FNAME	
FROM DATA.DBO.CDCALL

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 03/19/2013 :  11:45:51  Show Profile  Visit webfred's Homepage  Reply with Quote
You have to consider that there are entries without a comma and that's why your -1 errored.

Try to do your stuff only for rows where a comma exists i.e. add a where clause: where caller like '%,%'


Too old to Rock'n'Roll too young to die.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/19/2013 :  11:51:27  Show Profile  Reply with Quote
make it like

SELECT CALL#_CAD, caller, CAST(
			(
			LEFT
				(
				CALLER
				,CHARINDEX(',',CALLER + ',')-1
				)
			)
			AS VARCHAR(20)) AS LNAME	
FROM DATA.DBO.CDCALL


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

cstokes91
Yak Posting Veteran

USA
60 Posts

Posted - 03/20/2013 :  16:40:48  Show Profile  Reply with Quote
Thanks! Worked perfectly...

Now I am realizing something else...

On some of their records they have lname,fname mname with a space inbetween the fname and mname...

The code I have is pulling the mname correctly but for the first name it is grabbing everything...


SELECT 
	CALL#_CAD
	,caller
	,CAST(
			(
			LEFT
				(
				CALLER
				,CHARINDEX(',',CALLER + ',')-1
				)
			)
			AS VARCHAR(20)) AS LNAME	
	,CAST(
			(
			substring
				(
				CALLER
				,CHARINDEX(',',CALLER)+1
				,CASE
				WHEN CALLER LIKE '%[A-Z]'+' ' + '[A-Z]%' THEN CHARINDEX(' ', CALLER)
				ELSE LEN(CALLER)
				END			
				)
			)
			AS VARCHAR(15)) AS FNAME
	,CAST(
			(
			substring
				(
				CALLER
				,CHARINDEX(' ',CALLER)+1
				,len(caller)
				)
			)
			AS VARCHAR(15)) AS MNAME			
FROM DATA.DBO.CDCALL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/21/2013 :  02:00:45  Show Profile  Reply with Quote
so is it just space or space with a comma?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

cstokes91
Yak Posting Veteran

USA
60 Posts

Posted - 03/21/2013 :  09:34:22  Show Profile  Reply with Quote
LName,FName Mname

So a comma no space for LName and FName but a single space between FName and Mname

Here is some sample data (the last record obviously won't have a mname):
CLEMONS,PAMELA SUE
LORENZEN,HOPE LATKA
ANDERSON,MARY ELIZABETH
TILLETT,TERRY
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/22/2013 :  01:52:49  Show Profile  Reply with Quote

SELECT LEFT(SecondPart,CHARINDEX(' ',SecondPart+' ')-1) AS FName,
STUFF(SecondPart,1,CHARINDEX(' ',SecondPart+' '),'') AS MName,
FirstPart AS LName
FROM
(
SELECT LEFT(NameField,CHARINDEX(',',NameField+',')-1) AS FirstPart,
STUFF(NameField,1,CHARINDEX(',',NameField + ','),'') AS SecondPart
FROM table
)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
437 Posts

Posted - 03/22/2013 :  19:34:22  Show Profile  Reply with Quote

SELECT 
    CALL#_CAD, CALLER, 
    LEFT(CALLER, comma_position - 1) AS LNAME,
    SUBSTRING(CALLER, comma_position + 1, LEN(CALLER) - comma_position - space_position) AS FNAME,
    LTRIM(RIGHT(CALLER, space_position)) AS MNAME
FROM -- DATA.DBO.CDCALL cd
(
    --test data just as examples
    SELECT 1 AS CALL#_CAD, 'CLEMONS,PAMELA SUE' AS CALLER UNION ALL
    SELECT 2, 'LORENZEN,HOPE LATKA' UNION ALL
    SELECT 3, 'ANDERSON,MARY ELIZABETH' UNION ALL
    SELECT 4, 'TILLETT,TERRY'
) AS cd
CROSS APPLY (
    SELECT
        CHARINDEX(',', CALLER + ',') AS comma_position,
        CHARINDEX(' ', REVERSE(CALLER)) AS space_position
) AS cd_delims
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
60 Posts

Posted - 03/25/2013 :  15:53:46  Show Profile  Reply with Quote
Thanks for the input... I decided to go a little different of a route... I ended up running a separate update statement on the records.

UPDATE [ParkData].[dbo].[callerTest]
SET fname =
CASE
WHEN fname like '% %' then left(fname, CHARINDEX(' ',fname + ' ')-1)
ELSE FNAME
END
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000