insert parts of string into 5 columns

[bschmitz]

I have a 1000 character field in a table. I need to pull out different parts of the string from each record and insert them into different columns in a new table. I was thinking that I would create a DTS package to do this. I am new to SQL and find myself struggling with this. Thanks for the help.

 

[sguslan]

Suppose you have a table called tab1 that has the 1000 character field[called longfield] and the target table does not exist, then the following query execute by the SQL Query Analyzer would do the trick and create the target table[called newtable] as well:

select substring(longfield, 1, 600) Filed1,
substring(longfield, 601, 100) Filed2,
substring(longfield, 701, 100) Filed3,
substring(longfield, 801, 100) Filed4,
substring(longfield, 901, 100) Filed5

into newtable
from tab1


Now suppose the target table already exist, then the following would do:

Insert newtable
select substring(longfield, 1, 600) ,
substring(longfield, 601, 100) ,
substring(longfield, 701, 100) ,
substring(longfield, 801, 100) ,
substring(longfield, 901, 100)

from tab1

The toughest part of using the SUBSTRING function is to find what part of a field to strip , i.e. what is the position to start with and the length of characters to copy...

 

[bschmitz]

This would definitely work for all data that I knew the number of characters I wanted. This data is space delimited but the lengths can vary. Thanks!

 

[SQLSister]

look at Charindex function in Books online.

 

[sguslan]

Well , a cursor and another SQL function comes to help.
Charindex. MS has the doumentation you need to understand it.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_mta_01_8w1e.asp

The commands you're after look like this :

declare c cursor for select longfield from tab1
declare @str varchar(1000)
declare @pos int
declare @snippet1 varchar(1000)
declare @snippet2 varchar(1000)
declare @snippet3 varchar(1000)
declare @snippet4 varchar(1000)
declare @snippet5 varchar(1000)


OPEN c

FETCH NEXT FROM c
INTO @str

WHILE @@FETCH_STATUS = 0
BEGIN
--get first snippet
select @pos=charindex(' ',@str,1)
select @snippet1=substring(@str,1,@pos-1)
select @str=substring(@str,@pos+1,1000)

-- get snippet 2
select @pos=charindex(' ',@str,1)
select @snippet2=substring(@str,1,@pos-1)
select @str=substring(@str,@pos+1,1000)

-- get snippet 3
select @pos=charindex(' ',@str,1)
select @snippet3=substring(@str,1,@pos-1)
select @str=substring(@str,@pos+1,1000)
-- get snippet 4
select @pos=charindex(' ',@str,1)
select @snippet4=substring(@str,1,@pos-1)
select @str=substring(@str,@pos+1,1000)
-- get snippet 5
select @pos=charindex(' ',@str,1)
select @snippet5=substring(@str,1,@pos-1)
select @str=substring(@str,@pos+1,1000)

Insert newtable
select @snippet1,@snippet2,@snippet3,@snippet4,@snippet5

FETCH NEXT FROM c
INTO @str

END

CLOSE c
DEALLOCATE c
GO

 

[bschmitz]

Thanks squslan! I was able to use CHARINDEX and SUBSTRING to accomplish my task.