How to display only first 20 character

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to display only first 20 character

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-12 : 02:02:09

I've as follow,

declare @t1 table
(idx int identity(1,1), remk varchar(200));
insert into @t1 values('Offering custom-written SQL Server classes focusing on real-world expertise. Classes never cancelled');

How to display only first 20 character in remk, so the result as follow

idx |  remk
-------------------------------------------------------------------------
1	Offering custom-writ ...

need help

Kristen
Test

22859 Posts

Posted - 2010-06-12 : 02:27:24

[code]
declare @t1 table
(idx int identity(1,1), remk varchar(200));
insert into @t1 values('Offering custom-written SQL Server classes focusing on real-world expertise. Classes never cancelled');
insert into @t1 values('25--!----1----!----2----5'); -- Test 25 characters
insert into @t1 values('24--!----1----!----2---4'); -- Test 24 characters

SELECT idx, CASE WHEN LEN(remk) <= 24 THEN remk ELSE LEFT(remk, 20) + ' ...' END AS remk
FROM @t1
[/code]

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-12 : 02:32:38

tq sir

urzsuresh
Starting Member

30 Posts

Posted - 2010-06-12 : 05:52:39

quote:
Originally posted by Delinda

I've as follow,
declare @t1 table
(idx int identity(1,1), remk varchar(200));
insert into @t1 values('Offering custom-written SQL Server classes focusing on real-world expertise. Classes never cancelled');

How to display only first 20 character in remk, so the result as follow
idx | remk
-------------------------------------------------------------------------
1 Offering custom-writ ...

need help

Kindly try below one

declare @t1 table
(idx int identity(1,1), remk varchar(200));
insert into @t1 values('Offering custom-written SQL Server classes focusing on real-world expertise. Classes never cancelled');

Select idx,Left(remk,20) remk from @t1

Let me know that, do you need any further clarfication

Suri

Kristen
Test

22859 Posts

Posted - 2010-06-12 : 05:58:28

"Let me know that, do you need any further clarfication"

What does that achieve that my code doesn't?

urzsuresh
Starting Member

30 Posts

Posted - 2010-06-12 : 06:08:24

quote:
Originally posted by Kristen

"Let me know that, do you need any further clarfication"

What does that achieve that my code doesn't?

In that query, we no need to include case condition.

declare @t1 table
(idx int identity(1,1), remk varchar(200));
insert into @t1 values('Offering ');

Select idx,Left(remk,50) remk from @t1

here string length (i.e ('Offering ')) is less than 50. but i ihave used left keyword as 50. it wont through any error.value assign automatically

Suri

Kristen
Test

22859 Posts

Posted - 2010-06-12 : 09:01:41

Yeah, but you don't get the "..." on the end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-14 : 04:09:23

quote:

Originally posted by Kristen


declare @t1 table
(idx int identity(1,1), remk varchar(200));
insert into @t1 values('Offering custom-written SQL Server classes focusing on real-world expertise. Classes never cancelled');
insert into @t1 values('25--!----1----!----2----5');  -- Test 25 characters
insert into @t1 values('24--!----1----!----2---4');   -- Test 24 characters

SELECT idx, CASE WHEN LEN(remk) <= 24 THEN remk ELSE LEFT(remk, 20) + ' ...' END AS remk
FROM @t1

Why did you check for LEN(remk) <= 24?
I dont see that point from OP's question
Did the OP delete any replies?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-06-14 : 09:22:01

Requires 20 characters plus space & ellipse (3 chars)

Thus total 24 characters in total

Thus if original content is 24 chars, or less, it can be displayed (without ellipse)

If 25 chars, or more, than display first 20 and ellipse

So you get the effect of


XXX
XXXXX
XXXXXXXXXXX
YYYYYYY ...
YYYYYYY ...
YYYYYYY ...
XXXXXXXXX
XXXXXXXXXXX
XXX

where X represents "short" data values, and Y represents over-long values

Well ... that's how I've always understood it was supposed to be done ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-14 : 09:48:03

Ok. Let us wait till OP confirms it

Madhivanan

Failing to plan is Planning to fail

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-14 : 10:40:23

all of you are great.

tq to kristen, urzsuresh, and madhivanan :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources