comma delimited file

I am working with SQL Server 2000 table called dbo.proofofconcept".
I need to separate values that are all in one column that are space limited.
For example I have a column called tryit and the
value = '1234 456.75 01/01/2001 ABBR TRY@.GSU.ORG 75% $12.75 '.
I want to write a query so that the results can be split out to 7 output
columns. In other words, I would like the output to be:
Column 1 = 1234,
Column 2 = 456.75,
Column 3 = 01/01/2001,
Column 4 = ABBR,
Column 5 = TRY@.GSU.ORG,
Column 6 = 75%,
Column 7 = $12.75
The output would be separated into different columns for reporting purposes,
setup to create an output file, and possibly to be split into an XML file.
How would I go about writing a query like this? If this should be a stored
procedure, can you show me how I would write the stored procedure and how
would I execute the stored procedure?
Thanks!
Here's one way to do it. Depending on where this data originates,
you might also be able bulk insert or bcp it in, in which case the import
specifications would take care of splitting it up.
create table T (
[value] nvarchar(200)
)
go
insert into T values
('1234 456.75 01/01/2001 ABBR TRY@.GSU.ORG 75% $12.75 ')
go
select
Column1, Column2, Column3, Column4, Column5, Column6,
ltrim(left([value],charindex(space(1),[value])-1)) as Column7
from (
select
Column1, Column2, Column3, Column4, Column5,
ltrim(left([value],charindex(space(1),[value])-1))+',' as Column6,
ltrim(substring([value],charindex(space(1),[value]),200)) as [value]
from (
select
Column1, Column2, Column3, Column4,
ltrim(left([value],charindex(space(1),[value])-1))+',' as Column5,
ltrim(substring([value],charindex(space(1),[value]),200)) as [value]
from (
select
Column1, Column2, Column3,
ltrim(left([value],charindex(space(1),[value])-1))+',' as Column4,
ltrim(substring([value],charindex(space(1),[value]),200)) as [value]
from (
select
Column1, Column2,
ltrim(left([value],charindex(space(1),[value])-1))+',' as Column3,
ltrim(substring([value],charindex(space(1),[value]),200)) as
[value]
from (
select
Column1,
ltrim(left([value],charindex(space(1),[value])-1))+',' as
Column2,
ltrim(substring([value],charindex(space(1),[value]),200)) as
[value]
from (
select
ltrim(left([value],charindex(space(1),[value])-1))+',' as
Column1,
ltrim(substring([value],charindex(space(1),[value]),200))
as [value]
from T
) T1
) T2
) T3
) T4
) T5
) T6
GO
DROP TABLE T
Steve Kass
Drew University
Wendy Elizabeth wrote:

>I am working with SQL Server 2000 table called dbo.proofofconcept".
>I need to separate values that are all in one column that are space limited.
> For example I have a column called tryit and the
> value = '1234 456.75 01/01/2001 ABBR TRY@.GSU.ORG 75% $12.75 '.
>I want to write a query so that the results can be split out to 7 output
>columns. In other words, I would like the output to be:
> Column 1 = 1234,
> Column 2 = 456.75,
> Column 3 = 01/01/2001,
> Column 4 = ABBR,
> Column 5 = TRY@.GSU.ORG,
> Column 6 = 75%,
> Column 7 = $12.75
>The output would be separated into different columns for reporting purposes,
>setup to create an output file, and possibly to be split into an XML file.
> How would I go about writing a query like this? If this should be a stored
>procedure, can you show me how I would write the stored procedure and how
>would I execute the stored procedure?
> Thanks!
>
>
|||STEVE KASS:
THIS ANSWER IS EXTREMELY HELPFUL!
THANKS,
DIANE STEIN
"Steve Kass" wrote:

> Here's one way to do it. Depending on where this data originates,
> you might also be able bulk insert or bcp it in, in which case the import
> specifications would take care of splitting it up.
> create table T (
> [value] nvarchar(200)
> )
> go
> insert into T values
> ('1234 456.75 01/01/2001 ABBR TRY@.GSU.ORG 75% $12.75 ')
> go
> select
> Column1, Column2, Column3, Column4, Column5, Column6,
> ltrim(left([value],charindex(space(1),[value])-1)) as Column7
> from (
> select
> Column1, Column2, Column3, Column4, Column5,
> ltrim(left([value],charindex(space(1),[value])-1))+',' as Column6,
> ltrim(substring([value],charindex(space(1),[value]),200)) as [value]
> from (
> select
> Column1, Column2, Column3, Column4,
> ltrim(left([value],charindex(space(1),[value])-1))+',' as Column5,
> ltrim(substring([value],charindex(space(1),[value]),200)) as [value]
> from (
> select
> Column1, Column2, Column3,
> ltrim(left([value],charindex(space(1),[value])-1))+',' as Column4,
> ltrim(substring([value],charindex(space(1),[value]),200)) as [value]
> from (
> select
> Column1, Column2,
> ltrim(left([value],charindex(space(1),[value])-1))+',' as Column3,
> ltrim(substring([value],charindex(space(1),[value]),200)) as
> [value]
> from (
> select
> Column1,
> ltrim(left([value],charindex(space(1),[value])-1))+',' as
> Column2,
> ltrim(substring([value],charindex(space(1),[value]),200)) as
> [value]
> from (
> select
> ltrim(left([value],charindex(space(1),[value])-1))+',' as
> Column1,
> ltrim(substring([value],charindex(space(1),[value]),200))
> as [value]
> from T
> ) T1
> ) T2
> ) T3
> ) T4
> ) T5
> ) T6
> GO
> DROP TABLE T
> Steve Kass
> Drew University
> Wendy Elizabeth wrote:
>