text to varchar conversion woes

17 Mar 2006 3:52 PM

Stephan Steiner

Hi

I can't quite decide which is more annoying, string manipulation in
plain ANSI C without any helper libs, or in T-SQL. I've figured out the
copy text from one to another table, now I have to conversion problem.
Here's my query:

declare @workcodestring varchar(100)
select @workcodestring = CONVERT(varchar(100), (SELECT TOP 1 OC_NAME
FROM MSP_OUTLINE_CODES))

Which gives the usual message about text, etc. not being allowed in
subqueries. Since I can't declare any temp variables of type text, how
do I get the first 100 chars from OC_NAME which is of type TEXT?
Once I have it as varchar I need to proceed to conver the whole thing
to an int but that part I have figured out. The SELECT statement will
be somewhat more complex with a bunch of variables and a JOIN but I
figure in the end it's the same.

Regards
Stephan

17 Mar 2006 11:49 PM

Hugo Kornelis

On 17 Mar 2006 07:52:19 -0800, Stephan Steiner wrote:

(snip)

>Here's my query:
>
>declare @workcodestring varchar(100)
>select @workcodestring = CONVERT(varchar(100), (SELECT TOP 1 OC_NAME
>FROM MSP_OUTLINE_CODES))
>
>Which gives the usual message about text, etc. not being allowed in
>subqueries. Since I can't declare any temp variables of type text, how
>do I get the first 100 chars from OC_NAME which is of type TEXT?

Hi Stephan,

Try it with

SELECT TOP 1 @workcodestring = CONVERT(varchar(100), OC_NAME)
FROM MSP_OUTLINE_CODES

--
Hugo Kornelis, SQL Server MVP

18 Mar 2006 4:06 AM

Steve Kass

Stephan,

You should be able to do this:

set @workcodestring = (
select top 1 substring(OC_NAME,1,100)
from MSP_OUTLINE_CODES
)

Here is an example from Northwind:

declare @v varchar(100)
set @v = (
SELECT TOP 1 SUBSTRING(Notes,1,100) from Northwind..Employees
)
select @v

Steve Kass
Drew University

Stephan Steiner wrote:

Show quote

>Hi
>
>I can't quite decide which is more annoying, string manipulation in
>plain ANSI C without any helper libs, or in T-SQL. I've figured out the
>copy text from one to another table, now I have to conversion problem.
>Here's my query:
>
>declare @workcodestring varchar(100)
>select @workcodestring = CONVERT(varchar(100), (SELECT TOP 1 OC_NAME
>FROM MSP_OUTLINE_CODES))
>
>Which gives the usual message about text, etc. not being allowed in
>subqueries. Since I can't declare any temp variables of type text, how
>do I get the first 100 chars from OC_NAME which is of type TEXT?
>Once I have it as varchar I need to proceed to conver the whole thing
>to an int but that part I have figured out. The SELECT statement will
>be somewhat more complex with a bunch of variables and a JOIN but I
>figure in the end it's the same.
>
>Regards
>Stephan
>
>
>