Quick Question about Concatanation 1

[MichaelF81]

I have a table with 3 fields, em_id (vharchar 100), name_first (VarChar 50), name_last (varchar 50).

em_id has name info like "doe, john". I need to split the data and make name_first and name_last hold that info.

use wbtest
Update afm.em
Set    name_last = Left(em_id, CharIndex(', ', em_id) - 1),
       name_first = LTrim(Right(em_id, Len(em_id)) - CharIndex(', ', em_id))
WHERE name_first is null and name_last is null
GO

does not work, and it always returns

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

Ideas?




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[gmmastros]

The problem is that you have data without a comma in it. When this happens, you get the error.

Think about this...

Left(em_id, CharIndex(', ', em_id) - 1)

CharIndex will return the character position within the string. If ', ' isn't found, charindex returns 0, so your statement becomes...

Left(em_id, 0 - 1)

Or

Left(em_id, [!]-1[/!])

That -1 is the cause of your problem.

If you want to put the entire name in to the last name column when there is no comma, then you could use this query. As always with updates, make sure you have a good backup before running this.

Update afm.em
Set    name_last = Case When CharIndex(', ', em_id) > 0 
                        Then Left(em_id, CharIndex(', ', em_id) - 1)
                        Else em_id
                        End,
       name_first = Case When CharIndex(', ', em_id) > 0 
                         Then LTrim(Right(em_id, Len(em_id)) - CharIndex(', ', em_id))
                         Else ''
                         End
WHERE name_first is null and name_last is null

-George

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

 

[MichaelF81]

George,

I tried that and got the following error

quot[]Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'ian H ' to a column of data type int.[/quote]




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[gmmastros]

it's a 'parenthesis out of position' problem.

Try this...

Update afm.em
Set    name_last = Case When CharIndex(', ', em_id) > 0 
                        Then Left(em_id, CharIndex(', ', em_id) - 1)
                        Else em_id
                        End,
       name_first = Case When CharIndex(', ', em_id) > 0 
                         Then LTrim(Right(em_id, Len(em_id) - CharIndex(', ', em_id)))
                         Else ''
                         End
WHERE name_first is null and name_last is null

-George

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

 

[MichaelF81]

Once again George, you save my life again.




"Adults are just obsolete children and the hell with them." - Dr. Seuss