Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 how to convert text to varchar

Author  Topic 

sgpkalle
Starting Member

7 Posts

Posted - 2003-07-09 : 11:07:30
hi!

i hardly try to convert a field from text to varchar.
but the varchar-result only displays 29 charachters of
the text-original.
i used CONVERT as well as CAST. same result.

i know that varchar has a maximum size of 8000 - but no
matter: i'll never reach it. it would be enough for me to
reach 50 chars.

any convertion-hero out there?

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-09 : 11:23:35
Not sure what you're talking about...but try this in QA:



USE Northwind
GO

CREATE TABLE myTable99 (col1 text)
GO

DECLARE @x varchar(8000), @y int

SELECT @y = 0, @x = 'X'

WHILE @y < 8000
BEGIN
SELECT @X = @x + 'X', @y = @y + 1
END
INSERT INTO myTable99 (col1) SELECT @x

SELECT LEN(CONVERT(varchar(8000),col1)) AS NewCol1 FROM myTable99

DROP TABLE myTable99
GO





Brett

8-)
Go to Top of Page

sgpkalle
Starting Member

7 Posts

Posted - 2003-07-09 : 11:39:01
thx 2 u, brett!

but in this case i must not use a temporary table.

any other idea?

PS
because you wrote you don't know what i'm talking about:
i'm just trying to convert from the datatype text into varchar.
and my text-data won't get bigger than 8000 characters.

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-09 : 12:12:53
First, there are no temp tables in the example.

Second, you can cut and paste in QA to see it work. It disproves what you said:

quote:

but the varchar-result only displays 29 charachters



It may be you only have that many.

Third

quote:

i'm just trying to convert from the datatype text into varchar.
and my text-data won't get bigger than 8000 characters.



OK, it's already in the sample

Anything else you need help with (we are defintley NOT on the same page).

Got any code you'd care to post so I can see what you're doing?





Brett

8-)

Edited by - x002548 on 07/09/2003 12:13:45

Edited by - x002548 on 07/09/2003 12:14:55
Go to Top of Page

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-09 : 12:41:07
Hmmmm

I tried the following :



DECLARE @SOMETEXT CHAR(30)
SET @SOMETEXT = '123456789012345678901234567890'

PRINT @SOMETEXT

SET @SOMETEXT = CONVERT(VARCHAR(50), @SOMETEXT)

PRINT @SOMETEXT



...and it seems fine. I don't know what the problem is.

Shadow to Light
Go to Top of Page

sgpkalle
Starting Member

7 Posts

Posted - 2003-07-10 : 03:25:48
hi, brett!

ah... sorry... about your sample: that one with the temptable
was a misunderstanding. ok...

but i think you still didn't get my problem.
to see what it REALLY is, please add to your sample:

select convert(varchar,col1) from myTable99

(before dropping the table of course!)
do you still see the whole 8000-characters long line?

sgpkalle

Go to Top of Page

sgpkalle
Starting Member

7 Posts

Posted - 2003-07-10 : 03:30:28
hi, shadow to light!

sure as longe as you use "char" as original
dataformat there is no problem.
but there is another format called "text"
that can't be used as local variable. that's
why brett used a "temporary table" for it.

i have a table including a coloumn
of the dataformat "text". from this table i
select the "text"-coloumn and try to convert
it to "varchar":

select convert(varchar,textcoloumn) from mytable

and it display only the first 29 or 30 characters.

now clear?

sgpkalle

Go to Top of Page

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-10 : 03:55:55
This might help to show you something -

create table #moo (thing text)

insert into #moo values ('hi there how are you today are things going well I do hope so it would be so much better for everyone if they are')

select * from #moo

select convert (varchar,thing) from #moo
select convert (varchar(300),thing) from #moo

drop table #moo

-------
Moo. :)
Go to Top of Page

sgpkalle
Starting Member

7 Posts

Posted - 2003-07-10 : 04:13:01
ok, guys...

i found a solution by myself:

select convert(varchar(length),textcoloumn) from mytable

thanx anyway!

sgpkalle

Go to Top of Page

sgpkalle
Starting Member

7 Posts

Posted - 2003-07-10 : 04:14:09
ey... moo!

you've been faster, eh? ;)

sgpkalle

Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-10 : 11:11:03
Brett, Brett, Brett....

quote:
WHILE @y < 8000

BEGIN

SELECT @X = @x + 'X', @y = @y + 1

END

INSERT INTO myTable99 (col1) SELECT @x



How about:
INSERT INTO myTable99 (col1) REPLICATE('X', 8000)

Sam


Go to Top of Page

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-10 : 11:30:46
Sam,

Did you know about this function or is it something you came across on this forum.

I must admit that I would have done the same as Brett because I had no idea such function existed!

Even though it is cruel, I guess this is the best way to learn.

Thanks for the function.. I know I would have never found it.. and imagine posting a question asking about such a function... ouch.. I would get ripped to bits!

Regards.


---------------
Shadow to Light
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-10 : 11:52:11
First, there's very little ripping to bits in this forum. At least I haven't seen much.

Duck!



But I can't pass up an opportunity to tease those more SQL proficent (Like Brett) if I get a chance, which is not often at all.

In this case, REPLICATE isn't of any use in solving the problem in this thread, so I lose 5 points for critiquing an example, not a problem.

I lose 5 more points for the perception that the tease might be interpeted as cruel.

Take another 5 for good measure.

Anyway, there aren't many string functions. If you look in Books Online (aka BOL), REPLICATE is sitting there, and at the bottom of that page you'll find a link to a page that documents all the string functions.

I've never had a chance to use REPLICATE yet. Some of the key functions like CHARINDEX I use all the time.

Sam

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-10 : 12:27:35
[homer]dooooooooooooooooooooooooooohhh[homer]

Sam Plus 5 for keeping it light

Plus 5 for kicking me in my prverbial ass for being stupid

Plus 5 points because I got ripped by Nigel in another thread

Plus 5 points for reminding me that humility os everything (because you know, oh you know, that someone, somewhere, will ALWAYS be waiting).


Amethystium Minus 5...people don't get ripped for asking questions..if it comes across that way it's just general light hearted teasing (usually)...

Then there is him who shall not be named...the dark period...

Sam remebers....he left him that scar...




Brett

8-)
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-10 : 14:00:00
quote:

[homer]dooooooooooooooooooooooooooohhh[homer]


Then there is him who shall not be named...the dark period...

Sam remebers....he left him that scar...




You mean

[homer]dooooooooooooooooooooooooooohhh[/homer]

As for him who shall not be named I think Graz made him sorry he was ever born.

Jim
Users <> Logic

Edited by - jiml on 07/10/2003 14:01:59
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-10 : 14:20:25
quote:

You mean

[homer]dooooooooooooooooooooooooooohhh[/homer]



Thanks, forgot the ending tag. Be cool to have a little homer picture.

Why did I always think that a conversion with no size was always 1 byte?

Was that in 7.0? Anyway it seems all conversion without a size default to 30. I'm suprised though that it even let's you do that.



CREATE TABLE myTable99 (col1 text, col2 char(100), col3 varchar(2000))
GO
INSERT INTO myTable99 (col1, col2, col3) SELECT REPLICATE('x',5000), REPLICATE('x',100), REPLICATE('x',2000)
GO
SELECT LEN(Convert(varchar,col1)), LEN(Convert(varchar,col2)), LEN(Convert(varchar,col3)) FROM myTable99
GO
SELECT LEN(Convert(char,col1)), LEN(Convert(char,col2)), LEN(Convert(char,col3)) FROM myTable99
GO
DROP TABLE myTable99
GO





Brett

8-)
Go to Top of Page
   

- Advertisement -