SP to Break Up Column 2

[Gatorajc]

I am trying create a stored procedure to break a part a column and put it into 4 seperate fields.

The table looks something like this:

Route ID User Prospect Temp
-----------------------------------------
null null null null 5-W58-DJP-P

I want to it to end up like this

Route ID User Prospect Temp
-----------------------------------------
5 W58 DJP P null

I have no clue where to even start.

Also is there a way to check if there actually 4 parts to this 5-W58-DJP-P. So say something like 5-DJP-P it would return 0 or put error in another column. I know its a lot. I will be researching in the meantime. Thanks in advance.


AJ


If at first you do not succeed, cheat!

 

[rdroske]

Here you go, I was a little bored today.

No error checking but it won't crash on bad values and you can test them before the update. Assumes table name in question is table_name and your field names had to be changed to work in SQL.

Paste it into Query Analyzer and play with a test table to get it right for your situation.



DECLARE @string varchar(50),@val1 varchar(50),@val2 varchar(50),@val3 varchar(50),@val4 varchar(50)
DECLARE @position Int
declare tmpcursor cursor for select tempval from table_name
open tmpcursor
fetch next from tmpcursor into @string

while @@fetch_status = 0
BEGIN
SET @val1 = ''
SET @val2 = ''
SET @val3 = ''
SET @val4 = ''

SET @position = 1
WHILE @position <= DATALENGTH(@string) AND SUBSTRING(@string,@position,1) <> '-'
BEGIN
SET @val1 = @val1 + SUBSTRING(@string, @position, 1)
SET @position = @position + 1
END

SET @position = @position + 1
WHILE @position <= DATALENGTH(@string) AND SUBSTRING(@string,@position,1) <> '-'
BEGIN
SET @val2 = @val2 + SUBSTRING(@string, @position, 1)
SET @position = @position + 1
END

SET @position = @position + 1
WHILE @position <= DATALENGTH(@string) AND SUBSTRING(@string,@position,1) <> '-'
BEGIN
SET @val3 = @val3 + SUBSTRING(@string, @position, 1)
SET @position = @position + 1
END

SET @position = @position + 1
WHILE @position <= DATALENGTH(@string) AND SUBSTRING(@string,@position,1) <> '-'
BEGIN
SET @val4 = @val4 + SUBSTRING(@string, @position, 1)
SET @position = @position + 1
END

UPDATE table_name SET route=@val1,routeid=@val2,userinit=@val3,prospect=@val4,tempval=NULL WHERE tempval=@string

fetch next from tmpcursor into @string

END

close tmpcursor
deallocate tmpcursor

 

[nigelrivett]

update tbl
set Route = parsename(replace(Temp,'-','.'),4) ,
set ID = parsename(replace(Temp,'-','.'),4) ,
set User = parsename(replace(Temp,'-','.'),4) ,
set Prospect = parsename(replace(Temp,'-','.'),4)
where parsename(replace(Temp,'-','.'),4) is not null


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

That's as long as you don't have a . in a field - you could replace it with something you don't have and replace it back if you do.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[philhege]

Try this code:

delete from tmp_testparse
insert into tmp_testparse(ts_prospect)
values('5-W58-DJP-P')
insert into tmp_testparse(ts_prospect)
values('5-W58-DNZ')
insert into tmp_testparse(ts_prospect)
values('5-W44')
insert into tmp_testparse(ts_prospect)
values('5-W58-DQQ-P')
insert into tmp_testparse(ts_prospect)
values('5-O44-DJP-P')

select * from tmp_testparse
set nocount on
set transaction isolation level read uncommitted
--variable declaration
declare @l_vcucode varchar(12),
@l_ts_route varchar(30),
@l_vSQLString varchar(2048),
@l_vtemptable varchar(32),
@l_vshtracknolist varchar(2048),
@l_vshtracknolistmarker varchar(2048),
@l_ts_ID varchar(128),
@l_vshtrackno varchar(128),
@l_tsuser varchar(10),
@l_icounter int
select *, 0 as processed into #dec_tracknums from tmp_testparse
while (select count(*) from #dec_tracknums where processed = 0 ) > 0
BEGIN
--set the item listfor the current work record
select top 1 @l_vshtracknolist = ts_prospect from #dec_tracknums where processed = 0

--set the marker (in order to set the work record as processed)
set @l_vshtracknolistmarker = @l_vshtracknolist
--print @l_vshtracknolist


--loop through items and create an individual result record for each
set @l_icounter = 0
while len(rtrim(@l_vshtracknolist)) > 0
begin
set @l_icounter = @l_icounter + 1
--get the left-most tracking number
if charindex('-',@l_vshtracknolist) > 0
set @l_vshtrackno=substring(@l_vshtracknolist,1,charindex('-',@l_vshtracknolist)-1)
else
set @l_vshtrackno = @L_vshtracknolist
print 'Item '+cast(@L_icounter as char(1)) +': '+@l_vshtrackno

if @l_icounter = 1
update tmp_testparse set ts_route = @l_vshtrackno
where ts_prospect = @l_vshtracknolistmarker
if @l_icounter = 2
update tmp_testparse set ts_ID = @l_vshtrackno
where ts_prospect = @l_vshtracknolistmarker
if @l_icounter = 3
update tmp_testparse set ts_user = @l_vshtrackno
where ts_prospect = @l_vshtracknolistmarker
if @l_icounter = 4
update tmp_testparse set ts_prospect = @l_vshtrackno
where ts_prospect = @l_vshtracknolistmarker

--whack the left-most item from the working list
if charindex('-',@l_vshtracknolist) > 0
set @l_vshtracknolist = substring(@l_vshtracknolist,charindex('-',@l_vshtracknolist)+1,len(@l_vshtracknolist))
else
set @l_vshtracknolist = ''

print 'vshtracknolist now '+@l_vshtracknolist
--next item
end

-- set the working record as processed
update #dec_tracknums set processed = 1 where ts_prospect = @l_vshtracknolistmarker
--next record
END
select * from tmp_testparse

drop table #dec_tracknums

and adjust for your table name, etc.

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[philhege]

And, oh, here's the tmp_testparse table create SQL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_testparse]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tmp_testparse]
GO

CREATE TABLE [dbo].[tmp_testparse] (
[ts_pk] [int] IDENTITY (1, 1) NOT NULL ,
[ts_route] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ts_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ts_user] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ts_prospect] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Man, we're all bored today.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[rdroske]

Cursors are also usefull if you want your code to work which your doesn't nigelrivett. First too many Sets and if you fix that it puts the same value in each field.

Cursors are also nice if you need to deal with errors in the input which he stated he did.

Not bad though, I know a little SQL' just never used parsename.

I think what you meant was

update table_name
set Route = parsename(replace(Tempval,'-','.'),4) ,
routeid = parsename(replace(Tempval,'-','.'),3) ,
Userinit = parsename(replace(Tempval,'-','.'),2) ,
Prospect = parsename(replace(Tempval,'-','.'),1)
where parsename(replace(Tempval,'-','.'),4) is not null

(Field names changed to avoid reserved words)

Which work well but only if all you want to do in an error situation is nothing.

 

[nigelrivett]

Yep thanks - forgot to change things after copying rows.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

The invalid fields you can handle in another update or case statements.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Gatorajc]

Thought I had reponded to this. Sorry I like to give feedback.

In the end I went with nigelrivett's suggestion. Im all for simpler the better.



AJ


If at first you do not succeed, cheat!