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 ofthe 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 NorthwindGOCREATE TABLE myTable99 (col1 text)GODECLARE @x varchar(8000), @y intSELECT @y = 0, @x = 'X'WHILE @y < 8000 BEGIN SELECT @X = @x + 'X', @y = @y + 1 ENDINSERT INTO myTable99 (col1) SELECT @xSELECT LEN(CONVERT(varchar(8000),col1)) AS NewCol1 FROM myTable99DROP TABLE myTable99GO Brett8-) |
|
|
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?PSbecause 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. |
|
|
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.Thirdquote: 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 sampleAnything 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?Brett8-)Edited by - x002548 on 07/09/2003 12:13:45Edited by - x002548 on 07/09/2003 12:14:55 |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-07-09 : 12:41:07
|
HmmmmI tried the following :DECLARE @SOMETEXT CHAR(30)SET @SOMETEXT = '123456789012345678901234567890'PRINT @SOMETEXTSET @SOMETEXT = CONVERT(VARCHAR(50), @SOMETEXT)PRINT @SOMETEXT ...and it seems fine. I don't know what the problem is.Shadow to Light |
|
|
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 |
|
|
sgpkalle
Starting Member
7 Posts |
Posted - 2003-07-10 : 03:30:28
|
hi, shadow to light!sure as longe as you use "char" as originaldataformat there is no problem. but there is another format called "text"that can't be used as local variable. that'swhy brett used a "temporary table" for it. i have a table including a coloumn of the dataformat "text". from this table iselect the "text"-coloumn and try to convertit to "varchar":select convert(varchar,textcoloumn) from mytableand it display only the first 29 or 30 characters. now clear?sgpkalle |
|
|
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 #mooselect convert (varchar,thing) from #mooselect convert (varchar(300),thing) from #moodrop table #moo-------Moo. :) |
|
|
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 mytablethanx anyway!sgpkalle |
|
|
sgpkalle
Starting Member
7 Posts |
Posted - 2003-07-10 : 04:14:09
|
ey... moo!you've been faster, eh? ;)sgpkalle |
|
|
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 ENDINSERT INTO myTable99 (col1) SELECT @x
How about:INSERT INTO myTable99 (col1) REPLICATE('X', 8000)Sam |
|
|
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 |
|
|
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 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-10 : 12:27:35
|
[homer]dooooooooooooooooooooooooooohhh[homer]Sam Plus 5 for keeping it lightPlus 5 for kicking me in my prverbial ass for being stupidPlus 5 points because I got ripped by Nigel in another threadPlus 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...Brett8-) |
|
|
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.JimUsers <> LogicEdited by - jiml on 07/10/2003 14:01:59 |
|
|
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))GOINSERT INTO myTable99 (col1, col2, col3) SELECT REPLICATE('x',5000), REPLICATE('x',100), REPLICATE('x',2000)GOSELECT LEN(Convert(varchar,col1)), LEN(Convert(varchar,col2)), LEN(Convert(varchar,col3)) FROM myTable99GOSELECT LEN(Convert(char,col1)), LEN(Convert(char,col2)), LEN(Convert(char,col3)) FROM myTable99GODROP TABLE myTable99GO Brett8-) |
|
|
|