Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 21, 2016 3:58 PM
Points: 51, Visits: 759
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 @ 11:11 AM
Points: 14,199, Visits: 37,119
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1398459
Posted Wednesday, December 19, 2012 9:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 21, 2016 3:58 PM
Points: 51, Visits: 759
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