[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Extracting Summary from VARCHAR Data SQL SERVER 2000

Posted on 2007-07-31
5
Medium Priority
?
218 Views
Last Modified: 2008-09-08
Hi:
I already got help regarding this question but at the other end i got another issue:

Last Question: How to limit the varchar field size in SQL SERVER SELECT Statement!

Solution:
SELECT CAST( description  as varchar(100) ) Description
from  MyTable

Yes it worked for me but when CAST function trim the string it dont care about the complete words, as it some time cut the words (at the end of string) from middle. But i want to get complete words.

Example:
 (i want like that )
Description: This is a brief description of country....More

(i am getting like that)

Description: This is a brief description of cou

FYI: Technology: SQL SERVER 2000, C#




0
Comment
Question by:mmalik15
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 19600332
DECLARE @Val VARCHAR(100), @MaxLen INT
SELECT @MaxLen =10

SELECT @Val = 'Hello this is my world'

SELECT CASE WHEN LEN(@Val) > @MaxLen THEN
                                CASE WHEN LEFT(@Val,@MaxLen) = RTRIM(LEFT (@Val, @MaxLen+1 ) ) THEN LEFT(@Val,@MaxLen)
                                ELSE  LEFT(@Val,@MaxLen-CHARINDEX(' ',REVERSE(LEFT(@Val,@MaxLen) ) ) ) END
       ELSE @Val END
0
 

Author Comment

by:mmalik15
ID: 19600612
Can u please show me how to code in the query or i need to create a FUNCTION?

Thanks
0
 
LVL 15

Assisted Solution

by:dbbishop
dbbishop earned 1000 total points
ID: 19601018
Replace @Val with description (per your example). Add 'AS Description' after the last END


SELECT CASE WHEN LEN(description) > 100 THEN
                                CASE WHEN LEFT(description, 100) = RTRIM(LEFT (description, 100 + 1 ) ) THEN LEFT(description, 100)
                                ELSE  LEFT(description ,100 - CHARINDEX(' ',REVERSE(LEFT(description, 100) ) ) ) END
       ELSE description END AS Description
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

872 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