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.
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
cstokes91
Yak Posting Veteran
72 Posts |
Posted - 2013-03-21 : 09:34:22
|
LName,FName MnameSo a comma no space for LName and FName but a single space between FName and MnameHere is some sample data (the last record obviously won't have a mname):CLEMONS,PAMELA SUE LORENZEN,HOPE LATKA ANDERSON,MARY ELIZABETH TILLETT,TERRY |
|
|
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 LNameFROM(SELECT LEFT(NameField,CHARINDEX(',',NameField+',')-1) AS FirstPart,STUFF(NameField,1,CHARINDEX(',',NameField + ','),'') AS SecondPartFROM table)t[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 MNAMEFROM -- 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 cdCROSS APPLY ( SELECT CHARINDEX(',', CALLER + ',') AS comma_position, CHARINDEX(' ', REVERSE(CALLER)) AS space_position) AS cd_delims[/code] |
|
|
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 |
|
|
|
|
|
|
|