Re: comma delimited file
- From: Steve Kass <skass@xxxxxxxx>
- Date: Wed, 21 Sep 2005 00:45:52 -0400
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@xxxxxxx 75% $12.75 ')
goselect
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@xxxxxxx 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@xxxxxxx,
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!
.
- Prev by Date: Re: How to call talbe/SP from Linked server with out database and
- Next by Date: Re: space delimited values in one column
- Previous by thread: Re: How to call talbe/SP from Linked server with out database and user
- Next by thread: Re: space delimited values in one column
- Index(es):
Relevant Pages
|
|
