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 12»»

derived column Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 10:54 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346
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
Post #1351767
Posted Wednesday, August 29, 2012 11:57 AM


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: 2 days ago @ 2:04 PM
Points: 3,572, Visits: 8,008
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1351801
Posted Wednesday, August 29, 2012 12:09 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346
i need derived column solution ,not sql
Post #1351810
Posted Wednesday, August 29, 2012 12:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 5,078, Visits: 11,857
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1351816
Posted Wednesday, August 29, 2012 12:22 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: 2 days ago @ 2:04 PM
Points: 3,572, Visits: 8,008
I'm sorry, I didn't see the forum's name


Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1351826
Posted Wednesday, August 29, 2012 12:53 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346
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
Post #1351855
Posted Wednesday, August 29, 2012 1:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 5,078, Visits: 11,857
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1351878
Posted Wednesday, August 29, 2012 1:51 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346
i know ,it has so many variation.

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

plz help me
Post #1351896
Posted Wednesday, August 29, 2012 2:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 5,078, Visits: 11,857
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1351905
Posted Wednesday, August 29, 2012 7:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:04 PM
Points: 35, Visits: 98
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


Post #1351979
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse