|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to combine several long strings and then insert them to a text fieldDear all,
I have a question here, could anyone give me a help! I have 4 long varchar strings ,and I want to combile these for strings and then insert them to a text field, ps. the sum all size of these 4 strings is more than 8K How can I get that result Any helpful ideas would be appreciated! -ja INSERT INTO MyTable (MyTextColumn)
SELECT ( @String1 + ' ' + @String2 + ' ' + @String3 + ' ' + @String4 ) -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Joseph Anderson" <josnd***@yahoo.com> wrote in message news:ebNGJ7mwGHA.1624@TK2MSFTNGP02.phx.gbl... > Dear all, > I have a question here, could anyone give me a help! > > I have 4 long varchar strings ,and I want to combile these for > strings and then insert them to a text field, > > ps. the sum all size of these 4 strings is more than 8K > > How can I get that result > > Any helpful ideas would be appreciated! > > -ja > > Cool, thanks a lot!
-ja Show quote "Arnie Rowland" <ar***@1568.com> дÈëÏûÏ¢ÐÂÎÅ:uWolw8mwGHA.***@TK2MSFTNGP03.phx.gbl... > INSERT INTO MyTable (MyTextColumn) > SELECT ( @String1 + ' ' + @String2 + ' ' + @String3 + ' ' + @String4 ) > > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Joseph Anderson" <josnd***@yahoo.com> wrote in message > news:ebNGJ7mwGHA.1624@TK2MSFTNGP02.phx.gbl... >> Dear all, >> I have a question here, could anyone give me a help! >> >> I have 4 long varchar strings ,and I want to combile these for >> strings and then insert them to a text field, >> >> ps. the sum all size of these 4 strings is more than 8K >> >> How can I get that result >> >> Any helpful ideas would be appreciated! >> >> -ja >> >> > > Arnie,
If the OP was talking about SQL Server 2000, then this doesn't work. i.e. == create table t1 (b text) declare @s1 varchar(6000) set @s1 = REPLICATE('a', 6000) declare @s2 varchar(6000) set @s2 = REPLICATE('b', 6000) insert into t1 (b) select (@s1 + @s2) select datalength(b) from t1 == This returns 8000 as the datalength. Joseph, you might have to use routines such as UPDATETEXT to update the text in pieces. Stephen Show quote "Arnie Rowland" <ar***@1568.com> wrote in message news:uWolw8mwGHA.428@TK2MSFTNGP03.phx.gbl... > INSERT INTO MyTable (MyTextColumn) > SELECT ( @String1 + ' ' + @String2 + ' ' + @String3 + ' ' + @String4 ) > > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Joseph Anderson" <josnd***@yahoo.com> wrote in message > news:ebNGJ7mwGHA.1624@TK2MSFTNGP02.phx.gbl... >> Dear all, >> I have a question here, could anyone give me a help! >> >> I have 4 long varchar strings ,and I want to combile these for >> strings and then insert them to a text field, >> >> ps. the sum all size of these 4 strings is more than 8K >> >> How can I get that result >> >> Any helpful ideas would be appreciated! >> >> -ja >> >> > > Stephen and Joseph,
Inside EXEC, concatenation does not run into the 8000 character limit. So long as the strings you are dealing with can withstand replacing each quote (') with two quotes and still not exceed 8000 characters, this should work: create table t1 (b text) declare @s1 varchar(6000) set @s1 = REPLICATE('a', 6000) declare @s2 varchar(6000) set @s2 = REPLICATE('b', 6000) set @s1 = replace(@s1,char(39),char(39)+char(39)) set @s2 = replace(@s2,char(39),char(39)+char(39)) exec('insert into t1 (b) select ''' + @s1 + @s2 + '''') select datalength(b) from t1 go Steve Kass Drew University www.stevekass.com Show quote "Stephen Ahn" <noaddress> wrote in message news:Or0fdDowGHA.2400@TK2MSFTNGP06.phx.gbl... > Arnie, > > If the OP was talking about SQL Server 2000, then this doesn't work. > > i.e. > == > create table t1 (b text) > > declare @s1 varchar(6000) > set @s1 = REPLICATE('a', 6000) > > declare @s2 varchar(6000) > set @s2 = REPLICATE('b', 6000) > > insert into t1 (b) select (@s1 + @s2) > > select datalength(b) from t1 > == > > This returns 8000 as the datalength. > > Joseph, you might have to use routines such as UPDATETEXT to update the text in pieces. > > Stephen > > > "Arnie Rowland" <ar***@1568.com> wrote in message news:uWolw8mwGHA.428@TK2MSFTNGP03.phx.gbl... >> INSERT INTO MyTable (MyTextColumn) >> SELECT ( @String1 + ' ' + @String2 + ' ' + @String3 + ' ' + @String4 ) >> >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "Joseph Anderson" <josnd***@yahoo.com> wrote in message news:ebNGJ7mwGHA.1624@TK2MSFTNGP02.phx.gbl... >>> Dear all, >>> I have a question here, could anyone give me a help! >>> >>> I have 4 long varchar strings ,and I want to combile these for strings and then insert them to a text field, >>> >>> ps. the sum all size of these 4 strings is more than 8K >>> >>> How can I get that result >>> >>> Any helpful ideas would be appreciated! >>> >>> -ja >>> >>> >> >> > > Steve, Stephen and Arnie,
Yes, I tested all the functions and read some MSDN articles, you've been very helpful to me on this subject! Thanks a lot! -ja Show quote "Steve Kass" <sk***@drew.edu> дÈëÏûÏ¢ÐÂÎÅ:u8IcdRowGHA.4***@TK2MSFTNGP03.phx.gbl... > Stephen and Joseph, > > Inside EXEC, concatenation does not run into the 8000 character limit. > So long as the strings you are dealing with can withstand replacing > each quote (') with two quotes and still not exceed 8000 characters, this > should work: > > create table t1 (b text) > > declare @s1 varchar(6000) > set @s1 = REPLICATE('a', 6000) > > declare @s2 varchar(6000) > set @s2 = REPLICATE('b', 6000) > > set @s1 = replace(@s1,char(39),char(39)+char(39)) > set @s2 = replace(@s2,char(39),char(39)+char(39)) > exec('insert into t1 (b) select ''' + @s1 + @s2 + '''') > > select datalength(b) from t1 > go > > > Steve Kass > Drew University > www.stevekass.com > > "Stephen Ahn" <noaddress> wrote in message > news:Or0fdDowGHA.2400@TK2MSFTNGP06.phx.gbl... >> Arnie, >> >> If the OP was talking about SQL Server 2000, then this doesn't work. >> >> i.e. >> == >> create table t1 (b text) >> >> declare @s1 varchar(6000) >> set @s1 = REPLICATE('a', 6000) >> >> declare @s2 varchar(6000) >> set @s2 = REPLICATE('b', 6000) >> >> insert into t1 (b) select (@s1 + @s2) >> >> select datalength(b) from t1 >> == >> >> This returns 8000 as the datalength. >> >> Joseph, you might have to use routines such as UPDATETEXT to update the >> text in pieces. >> >> Stephen >> >> >> "Arnie Rowland" <ar***@1568.com> wrote in message >> news:uWolw8mwGHA.428@TK2MSFTNGP03.phx.gbl... >>> INSERT INTO MyTable (MyTextColumn) >>> SELECT ( @String1 + ' ' + @String2 + ' ' + @String3 + ' ' + >>> @String4 ) >>> >>> >>> -- >>> Arnie Rowland, Ph.D. >>> Westwood Consulting, Inc >>> >>> Most good judgment comes from experience. >>> Most experience comes from bad judgment. >>> - Anonymous >>> >>> >>> "Joseph Anderson" <josnd***@yahoo.com> wrote in message >>> news:ebNGJ7mwGHA.1624@TK2MSFTNGP02.phx.gbl... >>>> Dear all, >>>> I have a question here, could anyone give me a help! >>>> >>>> I have 4 long varchar strings ,and I want to combile these for >>>> strings and then insert them to a text field, >>>> >>>> ps. the sum all size of these 4 strings is more than 8K >>>> >>>> How can I get that result >>>> >>>> Any helpful ideas would be appreciated! >>>> >>>> -ja >>>> >>>> >>> >>> >> >> > > Steve, that's a neat trick.
On a related note, I've often wanted a proc to return an output blob parameter. eg : == create proc dbo.GetText @b text output as select @b = b from t1 == This won't compile because : == Server: Msg 409, Level 16, State 1, Procedure GetText, Line 2 The assignment operator operation cannot take a text data type as an argument. == In the past, I've to redefine procs like GetText to not use the output blob parameter. eg : == create proc dbo.GetText as select b from t1 == Do you know of some way to get the top GetText stored proc to work (assuming SQL Server 2000) ? Thanks, Stephen Show quote "Steve Kass" <sk***@drew.edu> wrote in message news:u8IcdRowGHA.4968@TK2MSFTNGP03.phx.gbl... > Stephen and Joseph, > > Inside EXEC, concatenation does not run into the 8000 character limit. > So long as the strings you are dealing with can withstand replacing > each quote (') with two quotes and still not exceed 8000 characters, this > should work: > > create table t1 (b text) > > declare @s1 varchar(6000) > set @s1 = REPLICATE('a', 6000) > > declare @s2 varchar(6000) > set @s2 = REPLICATE('b', 6000) > > set @s1 = replace(@s1,char(39),char(39)+char(39)) > set @s2 = replace(@s2,char(39),char(39)+char(39)) > exec('insert into t1 (b) select ''' + @s1 + @s2 + '''') > > select datalength(b) from t1 > go Stephen,
Though you can specify a parameter as "text output", I don't think there's a way to really make it work. The best suggestion I have is to see if what I posted here can help: http://groups.google.com/groups/search?q=1C7D09796D94 SK Show quote "Stephen Ahn" <noaddress> wrote in message news:OsBusuowGHA.1708@TK2MSFTNGP06.phx.gbl... > Steve, that's a neat trick. > > On a related note, I've often wanted a proc to return an output blob parameter. eg : > > == > create proc dbo.GetText @b text output as > select @b = b from t1 > == > > This won't compile because : > == > Server: Msg 409, Level 16, State 1, Procedure GetText, Line 2 > The assignment operator operation cannot take a text data type as an argument. > == > > In the past, I've to redefine procs like GetText to not use the output blob parameter. eg : > == > create proc dbo.GetText as > select b from t1 > == > > Do you know of some way to get the top GetText stored proc to work (assuming SQL Server 2000) ? > > Thanks, > Stephen > > > "Steve Kass" <sk***@drew.edu> wrote in message news:u8IcdRowGHA.4968@TK2MSFTNGP03.phx.gbl... >> Stephen and Joseph, >> >> Inside EXEC, concatenation does not run into the 8000 character limit. >> So long as the strings you are dealing with can withstand replacing >> each quote (') with two quotes and still not exceed 8000 characters, this >> should work: >> >> create table t1 (b text) >> >> declare @s1 varchar(6000) >> set @s1 = REPLICATE('a', 6000) >> >> declare @s2 varchar(6000) >> set @s2 = REPLICATE('b', 6000) >> >> set @s1 = replace(@s1,char(39),char(39)+char(39)) >> set @s2 = replace(@s2,char(39),char(39)+char(39)) >> exec('insert into t1 (b) select ''' + @s1 + @s2 + '''') >> >> select datalength(b) from t1 >> go > > > Stephen,
Though you can specify a parameter as "text output", I don't think there's a way to really make it work. The best suggestion I have is to see if what I posted here can help: http://groups.google.com/groups/search?q=1C7D09796D94 SK Show quote "Stephen Ahn" <noaddress> wrote in message news:OsBusuowGHA.1708@TK2MSFTNGP06.phx.gbl... > Steve, that's a neat trick. > > On a related note, I've often wanted a proc to return an output blob parameter. eg : > > == > create proc dbo.GetText @b text output as > select @b = b from t1 > == > > This won't compile because : > == > Server: Msg 409, Level 16, State 1, Procedure GetText, Line 2 > The assignment operator operation cannot take a text data type as an argument. > == > > In the past, I've to redefine procs like GetText to not use the output blob parameter. eg : > == > create proc dbo.GetText as > select b from t1 > == > > Do you know of some way to get the top GetText stored proc to work (assuming SQL Server 2000) ? > > Thanks, > Stephen > > > "Steve Kass" <sk***@drew.edu> wrote in message news:u8IcdRowGHA.4968@TK2MSFTNGP03.phx.gbl... >> Stephen and Joseph, >> >> Inside EXEC, concatenation does not run into the 8000 character limit. >> So long as the strings you are dealing with can withstand replacing >> each quote (') with two quotes and still not exceed 8000 characters, this >> should work: >> >> create table t1 (b text) >> >> declare @s1 varchar(6000) >> set @s1 = REPLICATE('a', 6000) >> >> declare @s2 varchar(6000) >> set @s2 = REPLICATE('b', 6000) >> >> set @s1 = replace(@s1,char(39),char(39)+char(39)) >> set @s2 = replace(@s2,char(39),char(39)+char(39)) >> exec('insert into t1 (b) select ''' + @s1 + @s2 + '''') >> >> select datalength(b) from t1 >> go > > > You're right. I should have prefaced with a disclaimer about SQL 2005 -not
2000, or used exec() as Steve K indicated. Thanks for the heads up to the OP. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Stephen Ahn" <noaddress> wrote in message news:Or0fdDowGHA.2400@TK2MSFTNGP06.phx.gbl... > Arnie, > > If the OP was talking about SQL Server 2000, then this doesn't work. > > i.e. > == > create table t1 (b text) > > declare @s1 varchar(6000) > set @s1 = REPLICATE('a', 6000) > > declare @s2 varchar(6000) > set @s2 = REPLICATE('b', 6000) > > insert into t1 (b) select (@s1 + @s2) > > select datalength(b) from t1 > == > > This returns 8000 as the datalength. > > Joseph, you might have to use routines such as UPDATETEXT to update the > text in pieces. > > Stephen > > > "Arnie Rowland" <ar***@1568.com> wrote in message > news:uWolw8mwGHA.428@TK2MSFTNGP03.phx.gbl... >> INSERT INTO MyTable (MyTextColumn) >> SELECT ( @String1 + ' ' + @String2 + ' ' + @String3 + ' ' + @String4 ) >> >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "Joseph Anderson" <josnd***@yahoo.com> wrote in message >> news:ebNGJ7mwGHA.1624@TK2MSFTNGP02.phx.gbl... >>> Dear all, >>> I have a question here, could anyone give me a help! >>> >>> I have 4 long varchar strings ,and I want to combile these for >>> strings and then insert them to a text field, >>> >>> ps. the sum all size of these 4 strings is more than 8K >>> >>> How can I get that result >>> >>> Any helpful ideas would be appreciated! >>> >>> -ja >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics