Problem in Substring function - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Problem in Substring function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-11 : 08:13:59

Harshi writes "Hello SQL Team!!

I have a problem in Substring function. There is a Text data type field in my Table and it has large no of characters. I want to get the whole text as the result. The problem is its just giving me 300 characters from a row, as the result.

My statement as follows;

//============================================================

SELECT a.L2Topic, a.TopicIndex, a.L1Topic,
Substring(b.Des, 1, 900), b.PriceGroup, b.Price,
b.L1TopicIndex, b.Qty, b.OtherPrice,
c.Customer, c.Address, c.Date, c.ChassyNo,
c.VehicleNo FROM wshpTopicIndex a

LEFT JOIN wshpEstimateDetails b ON (a.EstimateID = b.EstimateID)
INNER JOIN wshpEstimateIndex c ON (b.EstimateID = c.EstimateID)

WHERE (b.RandomNo = a.TopicIndex) AND (a.EstimateID = 54)

GROUP BY a.L2Topic, Substring(b.Des, 1, 900), c.Customer, a.TopicIndex, a.L1Topic, b.Qty,
b.PriceGroup, b.Price, b.L1TopicIndex, b.OtherPrice, c.Address, c.Date, c.ChassyNo, c.VehicleNo

ORDER BY b.PriceGroup, b.L1TopicIndex

//===============================================================

Can some one please help me to get rid of this problem??

Thank you,
Harshi"

Kristen
Test

22859 Posts

Posted - 2005-10-11 : 09:36:58

Hi Harshi, Welcome to SQL Team!

You are INNER JOINing a table to an OUTER JOINED table ...

LEFT JOIN wshpEstimateDetails b ON (a.EstimateID = b.EstimateID)
INNER JOIN wshpEstimateIndex c ON (b.EstimateID = c.EstimateID)

either you meant

LEFT JOIN wshpEstimateDetails b ON (a.EstimateID = b.EstimateID)
INNER JOIN wshpEstimateIndex c ON (a.EstimateID = c.EstimateID)

or I reckon you need some brackets there to keep the INNER JOIN "honest"!

And with

WHERE (b.RandomNo = a.TopicIndex)

you aren't going to get anything from A that is OUTER JOIN'd to B

But that said I can;t see any problem with your

Substring(b.Des, 1, 900)

statement - it should be giving you 900 characters.

if you are using Query Analyzer to view the results make sure that the OPTIONS are set to allow at least 900 characters per column.

If its some other Front End tool perhaps it has some similar setting that can be increased

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources