SQL Split and insert

[tmeers]

Hello all, I am trying to take a column of names currently seperated by a comma and split it into two seperate columns being firstname and last name.

This is what I have so far and it is failing with the error:
Server: Msg 207, Level 16, State 3, Line 10
Invalid column name 'GuarName'.

This is my script:

DECLARE @iPosition char(1)
DECLARE @FName varchar(500)
DECLARE @LName varchar(500)
select distinct GuarName from patientacc
where FinCode1 is null 
and FinCode2 is null 
and GuarName is not null 
and convert(smalldatetime,[Last Seen], 101) > '1/1/2000'
 
set @FName = GuarName
set @iPosition = ','
if (@FName is not null)
 begin
     WHILE (LTrim(RTrim(@FName)) <>'')
            BEGIN
                Set @iPosition = CHARINDEX(',',LTrim(RTrim(@FName)))
                if (@iPosition > 1)
                  begin
                        set @LName = Left(LTrim(RTrim(@FName)), @iPosition-1)
                        set @FName = RIGHT(LTrim(RTrim(@FName)), LEN(Ltrim(RTrim(@FName)))-@iPosition)    
                  end
                else
                  begin
                        set @LName = @FName
                        set @FName = ''
                  end
                if (Len(@LName) <> 0 )
                  begin
--                     insert into PatientAcc(GuarLName) Values(@LName)
select @LName
end end end

Hoping maybe some one could help me out on this.
Thanks a million,
Tim

[Delerna]

CHARINDEX(',',fieldname) returns the position of the first comma in a string

so left(fieldname,CHARINDEX(',',fieldname)-1) will return all the characters on the left of the comma

I'll let you figure out how to get the right hand side
:)

Regards

[Delerna]

OOPS
Just noticed you are already using charindex

line 4 select distinct GuarName from patientacc
line 29 Insert into PatientAcc(GuarLName) Values(@LName)

are the meant to be the same field, I guess not but.....


and should this set @FName = GuarName
be something like set @FName=(Select GuarName from .....)

[tmeers]

Well I guess that kinda helped but I did figure it out so kudos. Here is my code now, and it works well except for the fact that the machine it's running on cannot run it on the 16000+ row table oh well time to try it on another machine.

declare @FName varchar(50), @LName varchar(50), @iPosition char(1), @pt_id int
declare @get_name cursor
set @get_name = cursor for 
select pt_id, GuarName from patientacc
where GuarName is not null
 
open @get_name
 
fetch next from @get_name INTO @pt_id, @FName
 
while @@fetch_status = 0
fetch next from @get_name 
 
 
--SET @FName = GuarName
 
set @iPosition = ','
if (@FName is not null)
 begin
     WHILE (LTrim(RTrim(@FName)) <>'')
            BEGIN
                Set @iPosition = CHARINDEX(',',LTrim(RTrim(@FName)))
                if (@iPosition > 1)
                  begin
                        set @LName = Left(LTrim(RTrim(@FName)), @iPosition-1)
                        set @FName = RIGHT(LTrim(RTrim(@FName)), LEN(Ltrim(RTrim(@FName)))-@iPosition)    
                  end
                else
                  begin
                        set @LName = @FName
                        set @FName = ''
                  end
                if (Len(@LName) <> 0 )
                  begin
            Update patientacc
            set guarlname = @LName
            where pt_id = @pt_id
                     --insert into PatientAcc(GuarLName) Values(@LName)
--select @LName
end end end
 
close @get_name
deallocate @get_name

[Delerna]

You could try something like this, it will run a lot faster for you.
Set based updates (well set based anything really) will always be faster than cursors.
set based queries are easier to read and understand than cursors and therefore easier to modify at a later date when you have forgotten how it works.
never use a cursor unless you have exhaused set based possibilities
cursors should be your last resort.

 
UPDATE patientacc SET
guarLname=ltrim(   left(  guarname,charindex(',',guarname)-1  )    )
,guarname=rtrim(  right( guarname,len(guarname)-charindex(',',guarname)+1 )  )
where charindex(',',guarname) > 1
 
 

you might need to play around with the +1 and -1 to get the correct result, I can never remember straight off the top of my head.
Try it with a select first untill you get it right

 
SELECT Top 10
   ltrim(     left(  guarname,charindex(',',guarname)-1  )      )
   ,rtrim(  right(  guarname,len(guarname)-charindex(',',guarname)+1  )  )
FROM patientacc 
WHERE charindex(',',guarname) > 1
 
 

[tmeers]

PERFECT! well for the most part. Thank you VERY much for the help and best of all I have learned a bit about it. It's alway nice to see people help and teach at the same time. Oh and I have to do two -1's to get it here is the final and working code.

UPDATE patientacc SET
guarLname=ltrim( left( guarname,charindex(',',guarname)-1 ) )
,guarFname=rtrim( right( guarname,len(guarname)-charindex(',',guarname)-1 ) )
where charindex(',',guarname) > 1

Thanks a million. Now I'm off to convert some more data.
Tim