convert text to a table

I want to create a function which can convert text to a table...
let's said @.text='1,2,3,a,b'
select * from ConvertTextToTable(@.text)
it will return
key
--
1
2
3
a
b
Note: parameter must be text, not varchar.Britney wrote:
> I want to create a function which can convert text to a table...
> let's said @.text='1,2,3,a,b'
>
> select * from ConvertTextToTable(@.text)
>
> it will return
> key
> --
> 1
> 2
> 3
> a
> b
> Note: parameter must be text, not varchar.
You can't use text as a local variable in a stored procedure. Could you
explain better about how this code is executed and where the data comes
from. Are you wanting to write this as a stored procedure and a
function? If so, you could fetch the data from the table as text and
pass the value to the function. The function will need to parse the data
in the text parameter and insert into a table variable. I don't have
time to write the parsing routine, but the basic structure of the
function is as follows:
create function dbo.Text2Table (@.t text)
Returns @.Results Table (MyCol char(1)) -- check the column datatype
as
Begin
-- iterate through the text parameter and parse into a local variable
-- Insert into the table variable as each new value is found
Insert Into @.Results Values ('1')
Return
End
David Gugick
Quest Software
www.imceda.com
www.quest.com|||http://www.aspfaq.com/2248
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23Z0wAbjrFHA.1168@.TK2MSFTNGP11.phx.gbl...
>I want to create a function which can convert text to a table...
> let's said @.text='1,2,3,a,b'
>
> select * from ConvertTextToTable(@.text)
>
> it will return
> key
> --
> 1
> 2
> 3
> a
> b
> Note: parameter must be text, not varchar.
>
>|||well , your example is limited to 8000 varchar,
I want to write a function that accept longer than 8000,
that's why I said we must use TEXT datatype..
is it because function doesn't support TEXT as parameter?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eicRHojrFHA.1168@.TK2MSFTNGP11.phx.gbl...
> http://www.aspfaq.com/2248
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:%23Z0wAbjrFHA.1168@.TK2MSFTNGP11.phx.gbl...
>|||No, it's because you can't use a TEXT variable as a local variable. So, you
would have to do this in a much more complex way, e.g. insert the @.text
parameter directly into a table, and then loop through it using SUBSTRING
and PATINDEX to find the next comma. Ugh.
Are you really going to pass more than 8000 characters worth of
comma-separated values to a stored procedure? Have you considered doing
this parsing elsewhere?
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23ZlLlzjrFHA.460@.TK2MSFTNGP15.phx.gbl...
> well , your example is limited to 8000 varchar,
> I want to write a function that accept longer than 8000,
> that's why I said we must use TEXT datatype..
> is it because function doesn't support TEXT as parameter?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eicRHojrFHA.1168@.TK2MSFTNGP11.phx.gbl...
>|||yes... it's a big pain.
we have more than 8000 characters sometimes.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ufuOm4jrFHA.2076@.TK2MSFTNGP14.phx.gbl...
> No, it's because you can't use a TEXT variable as a local variable. So,
> you would have to do this in a much more complex way, e.g. insert the
> @.text parameter directly into a table, and then loop through it using
> SUBSTRING and PATINDEX to find the next comma. Ugh.
> Are you really going to pass more than 8000 characters worth of
> comma-separated values to a stored procedure? Have you considered doing
> this parsing elsewhere?
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:%23ZlLlzjrFHA.460@.TK2MSFTNGP15.phx.gbl...
>|||> yes... it's a big pain.
> we have more than 8000 characters sometimes.
Well, does this mean that sometimes there are 8,200 characters, or sometimes
there is 1 GB worth of data?
It might make sense for the application to do the parsing.
It might make sense for the application to split the strings up into 8,000
character chunks, and pass multiple parameters into the stored procedure.
It might make sense to call a single stored procedure multiple times for
each 8,000 character chunk that the app parses.
It might make sense to pass all 2 GB into the stored procedure as a TEXT
parameter, then parse/loop using SUBSTRING() and PATINDEX() as earlier
described.
Or it might make sense to redesign.
Who knows? We don't really have enough information about your app and what
is happening to this big comma-separated string to determine how to solve
this problem best.
A