December 18, 2008 at 9:55 am
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
December 18, 2008 at 10:42 am
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)
December 18, 2008 at 12:25 pm
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]
December 18, 2008 at 12:34 pm
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.
December 18, 2008 at 12:41 pm
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.
January 25, 2011 at 12:47 am
Thanks SSCraz,
You saved my time.
Randhir
January 9, 2013 at 12:04 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply