August 29, 2012 at 10:54 am
in my excel sheet i have 1 column
which contains name like
shah, anki S.
dogula nanacy
baskin brian B
waive, FRANCISCO p.
le, pinti
Leo, tomy
the first one is last name,firstname and middle initial
i need to seperate in all 3 column ,i am using derived column,but dont know expression.
please help me
August 29, 2012 at 11:57 am
How about this? It might be expensive, but it's a solution.
WITH names( fullname) AS(
SELECT 'shah, anki S.' UNION ALL
SELECT 'dogula nanacy' UNION ALL
SELECT 'baskin brian B' UNION ALL
SELECT 'waive, FRANCISCO p.' UNION ALL
SELECT 'le, pinti' UNION ALL
SELECT 'Leo, tomy'),
names2 AS(
SELECT REPLACE(REPLACE(REPLACE(fullname, '.',''),',', ''), ' ', '.') AS formatname
FROM names
)
SELECT REVERSE( PARSENAME(REVERSE(formatname), 1)),
REVERSE( PARSENAME(REVERSE(formatname), 2)),
REVERSE( PARSENAME(REVERSE(formatname), 3))
FROM names2
Or you can do it all at once:
WITH names( fullname) AS(
SELECT 'shah, anki S.' UNION ALL
SELECT 'dogula nanacy' UNION ALL
SELECT 'baskin brian B' UNION ALL
SELECT 'waive, FRANCISCO p.' UNION ALL
SELECT 'le, pinti' UNION ALL
SELECT 'Leo, tomy'
)
SELECT REVERSE( PARSENAME(REVERSE(REPLACE(REPLACE(REPLACE(fullname, '.',''),',', ''), ' ', '.')), 1)),
REVERSE( PARSENAME(REVERSE(REPLACE(REPLACE(REPLACE(fullname, '.',''),',', ''), ' ', '.')), 2)),
REVERSE( PARSENAME(REVERSE(REPLACE(REPLACE(REPLACE(fullname, '.',''),',', ''), ' ', '.')), 3))
FROM names
August 29, 2012 at 12:09 pm
i need derived column solution ,not sql
August 29, 2012 at 12:14 pm
harri.reddy (8/29/2012)
i need derived column solution ,not sql
Write out your detailed logic for breaking the column into three and we'll help with the solution. Might need a Script Component if it's complex - derived columns can only go so far.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 29, 2012 at 12:22 pm
I'm sorry, I didn't see the forum's name :blush:
August 29, 2012 at 12:53 pm
i am usinf this expression for my first column
REPLACE(SUBSTRING(product,1,FINDSTRING(product," ",1) + 0),","," ")
i need help for second and third.
please help me
August 29, 2012 at 1:25 pm
harri.reddy (8/29/2012)
i am usinf this expression for my first columnREPLACE(SUBSTRING(product,1,FINDSTRING(product," ",1) + 0),","," ")
i need help for second and third.
please help me
Not really what I asked for. What is your logic for dividing the second and third columns? It needs to accommodate all the variations in your data.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 29, 2012 at 1:51 pm
i know ,it has so many variation.
what is the other way in ssis, i can achieve.
plz help me
August 29, 2012 at 2:17 pm
harri.reddy (8/29/2012)
i know ,it has so many variation.what is the other way in ssis, i can achieve.
plz help me
Ah, you must mean the magic-wand component.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 29, 2012 at 7:51 pm
This should help :
Have a derived col. Have three cols in there as (assume F1 is your excel col):
First_name
expression :SUBSTRING((REPLACE(F1,","," ")),1,FINDSTRING(F1," ",1))
Last_name
expression :
SUBSTRING(F1,FINDSTRING(F1," ",1) + 1,FINDSTRING(F1 + " "," ",2) - FINDSTRING(F1," ",1) - 1)
middle_name
expression :
(FINDSTRING(F1," ",2) > 0) ? REPLACE(SUBSTRING(F1,FINDSTRING(F1," ",2) + 1,FINDSTRING(F1 + " "," ",3) - FINDSTRING(F1," ",2) - 1),".","") : " "
Please let me know if this worked or not
August 30, 2012 at 6:34 am
hi,
your last name formula is working as a middle name
and middle name formula is not working for last name,
i am getting all null value.
in my table i have char(1),
what column data type i need to place in derived column.
how to find last name
August 30, 2012 at 6:46 am
hi,
why we are using + 1 or -1 after findstring,please explain me
August 30, 2012 at 1:04 pm
the examples you had sent had the format :
Last Name, First Name, Middle Initial
So yes, granted, i interchanged the LastName and the FirstName labels. In my post, please understand 'FirstName' where it says LastName and the vice versa. Sorry about that.
As far as the middle initial goes, i tested all the expressions against the sample data that you had provided. It worked.
Please understand, the expressions i gave are obviously based on the assumption that there is only one space(and not two spaces) between the last and the first name. I took care of the comma through the conditional statemen.So it should work. Please try it first on the dataset that you had provided.
August 30, 2012 at 1:38 pm
its working.thanks a lot
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply