Split a column into 2 columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Split a column into 2 columns

Author

Topic

huslayer
Starting Member

10 Posts

Posted - 2010-02-16 : 13:27:10

Hello All,

I've this TEXT file, it's actually with the % delimiter.
I can import it using SSIS or import/export wizard in SQL 2008 with no problems.

My ONLY problem is one of the fields, which contains the name in this format "Doe, John" , and I need to separate the last name from the first name.

How can that be done?? Please help ...

I've attached the DB file, it's a public record DB from the state.

Also,

there'll be another big problem as the names are not listed that easily !!!

Example:

SINGH, SURINDER, M.D.
SINVANY-NUBEL, AVIVA R.N. AND A.P.N.
SKURATON, JEFFREY
SLAKIE, CHRISTINE, RN
SLATER, BARBARA A, L.P.N.
SLOTNICK, SINO
SLOVER, SHAYLA
SMITH DWAYNE
SMITH WILLIAM
SMITH, ALPHONSO, MD
SMITH, GEORGE R., JR.
SMITH, LAURA, LPN
SMITH, RUSELL III
SMITH, RUSSELL P., III LPN
SMITH, RUSSELL, III LPN
SMOLER, JOSEPH M, MD
SMUTEK, MICHELLE, M., LPN

which even makes it even harder to split them !!

Any clue?

Thank you so much

The file is located at : http://www.box.net/shared/h9ky1ju3r4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 13:28:44

for the above cases what should be first and last names? there are cases without , cases with two words,three words and even more

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

huslayer
Starting Member

10 Posts

Posted - 2010-02-16 : 13:32:04

visakh16,
Thank you for your answer...Actually this is what I'm trying to figure out ?

What do you think?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 13:43:02

quote:
Originally posted by huslayer

visakh16,
Thank you for your answer...Actually this is what I'm trying to figure out ?

What do you think?

Good question. I think it can be like first word alone being last name or initials also can become a part of it. so i'm not sure what your business interpretations are

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

huslayer
Starting Member

10 Posts

Posted - 2010-02-16 : 13:48:56

Yep that would do, first word alone will be the last name and any after will be the First Name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 13:59:33

then do like


SELECT REPLACE(LEFT(Yournamefield,CHARINDEX(' ',Yournamefield)-1),',','') AS LastName,
SUBSTRING(Yournamefield,CHARINDEX(' ',Yournamefield)+1,LEN(Yournamefield)) AS FirstName
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

huslayer
Starting Member

10 Posts

Posted - 2010-02-16 : 14:15:03

Hello,

I get this error when I run the code.

Msg 537, Level 16, State 2, Line 2
Invalid length parameter passed to the LEFT or SUBSTRING function.

here's the code:

SELECT REPLACE(LEFT(Name,CHARINDEX(' ',Name)-1),',','') AS LName,
SUBSTRING(Name,CHARINDEX(' ',Name)+1,LEN(Name)) AS FName
FROM [SISS_SMMC_Reports].[dbo].[DebardDB]

X002548
Not Just a Number

15586 Posts

Posted - 2010-02-16 : 14:29:24

most likely because it's an unknown value...ie. there's no comma

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

huslayer
Starting Member

10 Posts

Posted - 2010-02-16 : 14:39:57

Yep, I guess some names doesn't have commas !!

so how we can we use blank too?

X002548
Not Just a Number

15586 Posts

Posted - 2010-02-16 : 14:46:59

WHERE [Name] LIKE '%,%'

But that will cause a scan

It's only a 1 time deal though *, right

* Biggest lie in da bd'ness

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

huslayer
Starting Member

10 Posts

Posted - 2010-02-16 : 15:40:37

Can you give me the full code please, I tired, but get syntax error...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-17 : 01:21:39

quote:
Originally posted by huslayer

Can you give me the full code please, I tired, but get syntax error...

Post the code you tried

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 01:25:05

quote:
Originally posted by huslayer

Can you give me the full code please, I tired, but get syntax error...

Post how all your data can come. As your sample data showed there're always separated by a space which is what i assumed whereas error you posted shows that there may be cases even with single word alone

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

huslayer
Starting Member

10 Posts

Posted - 2010-02-17 : 08:08:11

Hello visakh16,

If we can separate the first word before the COMMA and add it as the last name and any after will be added as the last name.

That would be great.
the actual file is located at : http://www.box.net/shared/h9ky1ju3r4

Please take a look at it.

Thanks

quote:
Originally posted by visakh16

quote:
Originally posted by huslayer

Can you give me the full code please, I tired, but get syntax error...

Post how all your data can come. As your sample data showed there're always separated by a space which is what i assumed whereas error you posted shows that there may be cases even with single word alone

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-17 : 08:30:58

quote:
Originally posted by huslayer

Hello visakh16,

If we can separate the first word before the COMMA and add it as the last name and any after will be added as the last name.

That would be great.
the actual file is located at : http://www.box.net/shared/h9ky1ju3r4

Please take a look at it.

Thanks

quote:
Originally posted by visakh16

quote:
Originally posted by huslayer

Can you give me the full code please, I tired, but get syntax error...

Post how all your data can come. As your sample data showed there're always separated by a space which is what i assumed whereas error you posted shows that there may be cases even with single word alone

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Have you seen my previous reply?

Madhivanan

Failing to plan is Planning to fail

huslayer
Starting Member

10 Posts

Posted - 2010-02-17 : 08:52:43

Hello,
Sorry for that, Just saw your reply now.

this is the code i've used

SELECT REPLACE(LEFT(Name,CHARINDEX(' ',Name)-1),',','') AS LName,
SUBSTRING(Name,CHARINDEX(' ',Name)+1,LEN(Name)) AS FName
FROM [SISS_SMMC_Reports].[dbo].[DebardDB]

But I get this error when I run the code.

Msg 537, Level 16, State 2, Line 2
Invalid length parameter passed to the LEFT or SUBSTRING function.

quote:
Originally posted by madhivanan

quote:
Originally posted by huslayer

Can you give me the full code please, I tired, but get syntax error...

Post the code you tried

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 09:25:38

quote:
Originally posted by huslayer

Hello visakh16,

If we can separate the first word before the COMMA and add it as the last name and any after will be added as the last name.

That would be great.
the actual file is located at : http://www.box.net/shared/h9ky1ju3r4

Please take a look at it.

Thanks

quote:
Originally posted by visakh16

quote:
Originally posted by huslayer

Can you give me the full code please, I tired, but get syntax error...

Post how all your data can come. As your sample data showed there're always separated by a space which is what i assumed whereas error you posted shows that there may be cases even with single word alone

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Nope that wont work. As per your initial sample data there were cases which didnt have even a single ,. In such cases will you consider person as having no last name?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

huslayer
Starting Member

10 Posts

Posted - 2010-02-17 : 12:34:58

Thanks for everyone who tried to help, I did it using Script Component Transformation in SSIS and a C# script, and works like a charm.

I'll attach the script, maybe it could benefit someone...
God bless anybody who tried to share the knowledge.

/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}

public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
int firstCommaIndex=0;
firstCommaIndex = Row.Column7.IndexOf(",");
if (firstCommaIndex > 0)
{
Row.FirstName = Row.Column7.Remove(firstCommaIndex);
Row.LastName = Row.Column7.Substring(firstCommaIndex+1);
}

}

}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources