• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

Extracting Summary from VARCHAR Data SQL SERVER 2000

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
mmalik15
Asked:
mmalik15
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
mmalik15Author Commented:
Can u please show me how to code in the query or i need to create a FUNCTION?

Thanks
0
 
dbbishopCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now