Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

selecting column cuts off part of the data

Posted on 2014-03-11
3
Medium Priority
?
581 Views
Last Modified: 2014-03-11
I have a stored procedure and in it, I create a temp table, then dump some data into it. When I select from that temp table, one of my fields is getting cut off, even though my column definition is a lot more than the number of chars in the field. here's the code in question:

create table #tblResults (profilePicture varchar( 100 ), userDetails nvarchar(4000), offerText varchar( 20 ), goods varchar( 500 ), services varchar( 500 ), 
                         borrow varchar( 500 ), teach varchar( 500 ) );  

insert into #tblResults 
SELECT 
     userDetails = '<a href="/profile.asp?id=' + cast( u1.userID as varchar ) + '">' + u1.displayName + '</a>:&nbsp;' + cast( u2.aboutMe as varchar )
FROM #tblMatchedUsers u1 inner join tblUsers u2 on u1.userID = u2.userID 

Open in new window


the field "aboutMe" is the field that's getting cut off. In the table tblUsers, it is defined as a text field, and I'm assuming that's where the problem lies, even though the data in the record i'm testing is about 30 chars long.

Is there a way I can cast the whole thing as a text? when I tried that, I got errors.

Any help is greatly appreciated!

Cheers
Josh
0
Comment
Question by:Big Monty
  • 2
3 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39920760
When n is not specified when using the CAST and CONVERT functions, the default length is 30. ( see first line after Remarks )

Looks like in your SELECT line you are CAST( as varchar), but not defining the length, so the default of 30 kicks in.
0
 
LVL 34

Author Closing Comment

by:Big Monty
ID: 39920772
did not know that was the default, thx!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39920856
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

576 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question