February 22, 2006 at 9:40 am
I am using the following query to split a name column into FirtsName and LastName and insert into 2 different columns.
Update yourTable
Set FirstName=Substring(replace(NameCol,'.',','),1,charindex(', ',replace(NameCol,'.',','),,1)-1),
SecondName=Substring(replace(NameCol,'.',','),,charindex(', ',replace(NameCol,'.',','),,1)+2,len(NameCol))
but geting the following error
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ','.
February 22, 2006 at 9:58 am
too many commas...
declare @table table
(
NameCol varchar(500)
)
insert @table
select 'Bugs, Bunny'
union
select 'Elmer, Fudd'
union
select 'Daffy, Duck'
declare @result table
(
first_name varchar(100),
last_name varchar(100)
)
insert @result
Select FirstName=Substring(replace(NameCol,'.',','),1,charindex(', ',replace(NameCol,'.',','),1)-1),
SecondName=Substring(replace(NameCol,'.',','),charindex(', ',replace(NameCol,'.',','),1)+2,len(NameCol))
from @table
select * from @result
HTH
Mathew J Kulangara
sqladventures.blogspot.com
February 22, 2006 at 10:05 am
I need to update the existing table
February 22, 2006 at 10:24 am
easy enough......
Update yourTable
Select FirstName=Substring(replace(NameCol,'.',','),1,charindex(', ',replace(NameCol,'.',','),1)-1),
SecondName=Substring(replace(NameCol,'.',','),charindex(', ',replace(NameCol,'.',','),1)+2,len(NameCol))
Mathew J Kulangara
sqladventures.blogspot.com
February 22, 2006 at 10:37 am
Ok I got it worked but how can i delete last char in my FirstName column, coz I have ',' there which i dont need it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply