Split field into two based on "\" character 1

[KARR]

All,

I have a table with a field called "Name" containing date in this format:

LastName\FirstName

Example:

Record1 Brown\Joe
Record2 Smith\Rob

and would like to have it split the names and create two new fields:

Example:
LastName FirstName
Record1 Brown Joe
Record2 Smith Rob

I looked through the SQL Books online with no success, any ideas?

 

[cfaulkner]

Use Patindex in conjuction with substring or left/right.

PATINDEX
Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

 

[jbenson001]

Try this:

select Left([name], CharIndex('\', [name]) - 1),
       SubString([name], CharIndex('\', [name]) + 1, Len([name]) - CharIndex('\', [name]) + 1)

from table1

 

[KARR]

jbenson001: Tried it and returned...

Invalid length parameter passed to the substring function.

This is what I have so sfar:

SELECT PATINDEX('%/%',_Name) as Position
FROM Master

Which returns the position of the / character as "Position"

Now working on this, which I am not sure if its right:

SELECT PATINDEX('%/%',Def1_Name) as Position,
Substring (Def1_Name, 1, [Position] -1)
FROM Master

Which should return everything before the / but it is not.

 

[KARR]

Note, all my fields names are "Name" and not "Def1_Name" that was just a typo.

 

[cfaulkner]

jbenson001's code looks good and charindex is a better choice then patindex. Give his suggestion a try.

 

[jbenson001]

I tried my code with the data you supplied and some of my own and it worked fine. Give it a try and let me know.

 

[KARR]

Just did and is returning: Invalid length parameter passed to the substring function.

 

[jbenson001]

It could be that some of your data does not conatain a "\" in it.

 

[KARR]

Got it to work in a new test table, however the table with the production data keeps failing the query. I will have to look into the design of the table to see why its failing.

Now, that I have the names split in this table, how could I store these new fields back into the table?

Thanks

Rob

 

[jbenson001]

Update <table>

Update table1
Set LastName = Left([name], CharIndex('\', [name]) - 1),
FirstName = SubString([name], CharIndex('\', [name]) + 1, Len([name]) - CharIndex('\', [name]) + 1)

From table1

[/code}

 

[KARR]

Thanks, I will try the update table code in a few minutes.

Found out why the original table failed, it seems not all of the fields are populated with Last\First, some are blank and were causing the error.

So...would I be able to correct this with an IsNull?

 

[jbenson001]

Yes try using IsNull if NULLs are actually in there and also compare and see if it is blank ( = '')

 

[SQLBill]

As a side question, why are you creating tables in the MASTER database?

FROM Master

That is a REALLY BAD thing to do. I hope that's really not the database you are working in and you just used that name as a 'cover'.

-SQLBill

Posting advice: FAQ481-4875

 

[KARR]

^ No no, one of the tables in a totally different database is called 'Master', we arent using the Master Database.

 

[SQLBill]

Okay, my mistake...I should have caught that...TABLE not DATABASE. It's been a long week......

-SQLBill

Posting advice: FAQ481-4875

 

[KARR]

Hmm...trying to get the IsNull function in the Update statement with no luck. This is what I have...

Use Test

Update Names

Set LastName = Left([name], CharIndex('/', [name]) - 1),
FirstName = SubString([name], CharIndex('/', [name]) + 1, Len([name]) - CharIndex('/', [name]) + 1)

From Names

Thoughts?

 

[gmmastros]

Only update columns that have info in the column, like this.

Update Names

Set LastName = Left([name], CharIndex('/', [name]) - 1),
FirstName = SubString([name], CharIndex('/', [name]) + 1, Len([name]) - CharIndex('/', [name]) + 1)

From Names

Where IsNull([Name], '') <> ''  And CharIndex('/', [Name]) > 0

You may still have problems if there are records without the slash in the field.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

I don't think NULL values in the name column are the problem, I tried it and I don't get an error. I think the problem is where you have something in the name field that does not contain the'/' character, this will cause an error

You can change the Update to this:

Update Names

Set LastName = Left([name], CharIndex('/', [name]) - 1),
FirstName = SubString([name], CharIndex('/', [name]) + 1, Len([name]) - CharIndex('/', [name]) + 1)

From Names

Where 
   CharIndex('/', [name]) > 0

 

[KARR]

jbenson,

Ahh..that did it.

Thanks much.