Solved

selecting column cuts off part of the data

Posted on 2014-03-11
3
533 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 65

Accepted Solution

by:
Jim Horn earned 500 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 33

Author Closing Comment

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

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

808 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