how to split a field?

Question

how to split a field?

bgargiul

Newbie

Points: 3
More actions
May 19, 2003 at 7:58 am

#81617

I have an old system that I need to import data from. The database exports to a text file; each field is a set size. However the size and number of fields after the 1st 6 depends on what is in the 2nd field.
I imported the "mixed data" at the end into one field in a holding table hoping to be able to split it depending on the record in the 2nd field? How can I do this if at all possible?
Thank you for any help you can give me!
Brian

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi,

it should be possible to use conditional processing depending on what value is in the second field. Can you provide more information on what value field 2 can contain and what should be done with it?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

bgargiul Newbie Points: 3 More actions · Answer 2

Thanks for the help,

Field 2 contains 1 character. A,B,C,D,or E.

In its simplest form - if that field = D then the last field will be split into 2 fields, one that is 60 characters and the other set to 87. There may be no data at all in some of the fields imported but the 2nd field always contains a value of A-E.

If you need more info please let me know and thank you again for the help.

Brian

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi,

sorry for being that late!

You can write a script in QA to handle this. As this is hopefully one-time action, I think a cursor is quite ok for it.

use zz_fai_play --make sure you're using the correct db

go

declare @last_field varchar(255) -- variable to hold the string to be splitted

declare @condition char(1) --variable to hold value of field 2

declare @new_field_first varchar(60)

declare @new_field_second varchar(87)

DECLARE NEWCURSOR CURSOR FOR

SELECT <field2,lastfield> FROM <yourtable>

OPEN NEWCURSOR

FETCH NEXT FROM NEWCURSOR INTO @condition, @last_field

WHILE ( @@FETCH_STATUS<>-1 )

BEGIN

if @condition='D' --you can add more conditions to handle A,B,C,E

begin

set @new_field_first = left(@last_field,60)

set @new_field_second = substring(@last_field,61,87)

--here should go some validation action and then some insert action

end

FETCH NEXT FROM NEWCURSOR INTO @condition, @last_field

END

DEALLOCATE NEWCURSOR

You maybe have to modify this a little to fit your needs, but then it should work

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

bgargiul Newbie Points: 3 More actions · Answer 4

Thanks a bunch. You probably saved me a few days more of frustration!

Brian