Extracting Summary from VARCHAR Data SQL SERVER 2000

Posted on 2007-07-31
Last Modified: 2008-09-08
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!

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.

 (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#

Question by:mmalik15
    LVL 75

    Accepted Solution

    DECLARE @Val VARCHAR(100), @MaxLen INT
    SELECT @MaxLen =10

    SELECT @Val = 'Hello this is my world'

                                    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

    Author Comment

    Can u please show me how to code in the query or i need to create a FUNCTION?

    LVL 15

    Assisted Solution

    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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now