Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to devied fullname into firstname and lastname Expand / Collapse
Author
Message
Posted Thursday, December 18, 2008 9:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2008 9:30 AM
Points: 23, Visits: 90
Hi

in my source i have fullname column it has

fullname
--------
RobertJain
JansonStuart
IsmailSummit
JohnAbharaham

in my destination i have change this fullname column into firstname column and lastname column

firstname Lastname
-------- --------
Robert Jain
Janson Stuart
Ismail Summit
John Abharaham

How do i have to do this in SSIS

Thanks
vasu
Post #622258
Posted Thursday, December 18, 2008 10:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 1, 2014 1:26 PM
Points: 2,242, Visits: 3,645

I'm not sure if charindex supports wildcard....

--Assumption 1 - FirstName and LastName are both Not Null
--Assumption 2 - There are only 2 capital letters and they are first character of first name and first character of last name

declare @fullname varchar(200)
declare @myLen int
Declare @start int
Declare @pos int
Declare @firstName varchar(200)
declare @lastName varchar(200)
@fullname='RobertJain'
@l=len(@myString)
@start=2
While (@start<=@l)
BEGIN
@c=substring( @fullname ,@start,1)
if ascii(@c) between 65 and 90
Begin
@pos=@start
BREAK
end
end


@FirstName=left( @fullname , @Pos-1)
@LastName=right( @fullname , @l-@pos-1)





Pradeep Singh
Post #622317
Posted Thursday, December 18, 2008 12:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:44 AM
Points: 2,897, Visits: 5,980
If you'd like something that's set based and will most likely perform a bit better than looking at each individual character give the following a shot... Like the above I made an assumption that the lastname starts with a capital, no requirement that the firstname be capitalized.

Also you'll see the number 1000 used in my character expressions, you can adjust that down to the limit of your column left or actually do the Len calculation if you like... Just remember to Keep the Collation as that's what gives you the case sensative bit...

CREATE TABLE #test (
fullname VARCHAR(20)
)

INSERT INTO [#test]
SELECT 'RobertJain' UNION ALL
SELECT 'JansonStuart' UNION ALL
SELECT 'IsmailSummit' UNION ALL
SELECT 'JohnAbharaham'

SELECT [fullname],
LEFT([fullname], PATINDEX ('%[A-Z]%', SUBSTRING([fullname], 2, 1000) COLLATE Latin1_General_BIN)) AS FirstName,
SUBSTRING(fullname, PATINDEX ('%[A-Z]%', SUBSTRING([fullname], 2, 1000) COLLATE Latin1_General_BIN)+1, 1000) AS LastName
FROM [#test]

DROP TABLE [#test]



To help us help you read this

For better help with performance problems please read this
Post #622403
Posted Thursday, December 18, 2008 12:34 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 3,135, Visits: 11,477
This is not an easy problem to generalize.

Take this name for example: “Carl von Clausewitz”. Unless the procedure knows the conventions, how does it identify that the last name is two words, “von Clausewitz”?

Names are full of exceptions like this, and not all cultures even follow the convention of having a first name and last name.


Post #622412
Posted Thursday, December 18, 2008 12:41 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:44 AM
Points: 2,897, Visits: 5,980
That's absolutely true however given the situation and more importantly the sample data, the only real solution is run the script hoping to catch most of the names followed by a review to correct the ones that were wrong. 'Tis the reason why you never, ever, ever store a name all together like this no matter if you have no reason why you'd ever need it split. Inevitably, you'll need to do it.

-Luke.


To help us help you read this

For better help with performance problems please read this
Post #622414
Posted Tuesday, January 25, 2011 12:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 2, 2014 4:04 AM
Points: 100, Visits: 339
Thanks SSCraz,
You saved my time.

Randhir
Post #1052900
Posted Wednesday, January 9, 2013 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:10 PM
Points: 2, Visits: 16
Divide.
Sorry to be the grammer nazi.
I have to agree, this is a bad way to store names.
Our test cases:

De Leon Smith
J.Donald Smith
Betty Rae McCollum-Smith
Ray Jay Johnston Jr.
Dr. John McCormick III
Huyan Fan Vick

These would all probably break the example code. The last one is common in our area, the family name is Huyan, similar to the traditional last name.
We keep both a Fname Lname, Minit, and a display name to get around most of the issues in our HR database.
Hope that gives you some food for thought. -K
Post #1404935
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse