Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to devied fullname into firstname and lastname


how to devied fullname into firstname and lastname

Author
Message
vasu.ssis
vasu.ssis
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 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
ps.
ps.
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2729 Visits: 3668
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
Luke L
Luke L
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3368 Visits: 6123
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
Michael Valentine Jones
Michael Valentine Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4722 Visits: 11771
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.
Luke L
Luke L
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3368 Visits: 6123
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
Randhir Singh
Randhir Singh
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 364
Thanks SSCraz,
You saved my time.

Randhir
cake235
cake235
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search