derived column

Question

Post reply

derived column

harri.reddy

SSCommitted

Points: 1957
More actions
August 29, 2012 at 10:54 am

#266778

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

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

harri.reddy SSCommitted Points: 1957 More actions · Answer 2

harri.reddy

SSCommitted

Points: 1957

August 29, 2012 at 12:09 pm

#1531554

i need derived column solution ,not sql

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

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.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

I'm sorry, I didn't see the forum's name :blush:

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

harri.reddy SSCommitted Points: 1957 More actions · Answer 5

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

Phil Parkin SSC Guru Points: 246686 More actions · Answer 6

harri.reddy (8/29/2012)
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

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.

harri.reddy SSCommitted Points: 1957 More actions · Answer 7

i know ,it has so many variation.

what is the other way in ssis, i can achieve.

plz help me

Phil Parkin SSC Guru Points: 246686 More actions · Answer 8

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.

n79799 SSC Eights! Points: 804 More actions · Answer 9

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

harri.reddy SSCommitted Points: 1957 More actions · Answer 10

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

harri.reddy SSCommitted Points: 1957 More actions · Answer 11

hi,

why we are using + 1 or -1 after findstring,please explain me

n79799 SSC Eights! Points: 804 More actions · Answer 12

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.

harri.reddy SSCommitted Points: 1957 More actions · Answer 13

harri.reddy

SSCommitted

Points: 1957

August 30, 2012 at 1:38 pm

#1532104

its working.thanks a lot