Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Splitting up LNAME, FNAME

Author  Topic 

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-03-19 : 11:38:47
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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-19 : 11:45:51
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

52326 Posts

Posted - 2013-03-19 : 11:51:27
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

72 Posts

Posted - 2013-03-20 : 16:40:48
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

52326 Posts

Posted - 2013-03-21 : 02:00:45
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

72 Posts

Posted - 2013-03-21 : 09:34:22
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

52326 Posts

Posted - 2013-03-22 : 01:52:49
[code]
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
[/code]

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

Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-03-22 : 19:34:22
[code]
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
[/code]
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-03-25 : 15:53:46
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
   

- Advertisement -