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

Substring Query to pull firstname and lastname out of one column Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 8:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:42 AM
Points: 47, Visits: 612
I Have a table with a field called name that is formated as the examples below. I need to pull the firstname as firstname and the lastname as lastname and the DOB as DOB. DOb is the first date in the field.
I am having a hard time getting it to pull between the -.

Basic format: these will all have the same number of '-' but there could be an extra initial in the name as the sample below shows for A. Marion Groseth. how can I get it to get past those names with the extra initial as well?

HIST-AS-JEFFERY WINKEL-02/26/1976-07/06/2012
HIST-AS-JOSEPH WALDNER-07/07/1965-07/06/2012
HIST-AS-TERESA KLINE-10/09/1965-02/13/2012
HIST-HS-A. MARION GROSETH-11/10/1931-01/19/2009
HIST-HS-AARON ALFRED-07/06/1967-06/02/2010
Post #1398447
Posted Wednesday, December 19, 2012 8:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 12,927, Visits: 32,332
well, one way is to use the delimitedSplit8K function to chop the string into bitesize peices.

from there, you might need to cross apply, modifying the WHERE statement to limit it to teh data that's mostly correct.

take alook at this example...you'll see how the name is chopped into 2 or 3 pieces per ID, based on your sample data.

With mySampleData(Id,String)
AS
(
SELECT 1,'HIST-AS-JEFFERY WINKEL-02/26/1976-07/06/2012' UNION ALL
SELECT 2,'HIST-AS-JOSEPH WALDNER-07/07/1965-07/06/2012' UNION ALL
SELECT 3,'HIST-AS-TERESA KLINE-10/09/1965-02/13/2012' UNION ALL
SELECT 4,'HIST-HS-A. MARION GROSETH-11/10/1931-01/19/2009' UNION ALL
SELECT 5,'HIST-HS-AARON ALFRED-07/06/1967-06/02/2010'
)

select
mySampleData.*,
ByDashes.*,
BySpaces.*
from mySampleData
CROSS APPLY dbo.DelimitedSplit8K(mySampleData.String,'-') ByDashes
CROSS APPLY dbo.DelimitedSplit8K(ByDashes.Item,' ') BySpaces
WHERE ByDashes.ItemNumber = 3 --the 3rd dash group has the values we want to further split by spaces

{edit}
here's the article containing the DelimitedSplit8K funcrtion:
http://www.sqlservercentral.com/articles/Tally+Table/72993/


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1398459
Posted Wednesday, December 19, 2012 9:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:42 AM
Points: 47, Visits: 612
That works great. I had never seen that function scipt before. I just had to then create a script to catch the firstname , lastname and initial based on the ID and item numbers into the columns I needed.

Thanks.
Post #1398513
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse