CODEWITHSUNDEEP
sqlvb.netstringtext
Ortund
Ortund

Reputation: 8255

Meaningful string limiting

I've got rows in my table that contain text anywhere between 31 character and 281 characters long.

Rather than display all this text in my table on my webpage, I'd like to limit this text to a few characters long so as to identify it to the user.

What I've done is select left(ColumnName, 30) but this way is fairly ugly on the page as it cuts off the text in the middle of words. Can anyone suggest a way to break it on a character or something that would display better on my page?

Upvotes: 1

Views: 52

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44336

Code:

declare @columnname nvarchar(281)
set @columnname = 'Rather than display all this text in my table on my webpage, I''d like to limit this text to a few characters long so as to identify it to the user.'

select case when charindex(' ', @columnname, 30) > 30 
  then left(@columnname, charindex(' ', @columnname, 30)) else @columnname end

Result:

Rather than display all this text

Upvotes: 3

John N
John N

Reputation: 1815

One way to handle it would be to modify your existing solution with the CHARINDEX function, as below:

select left(ColumnName, charindex(' ', ColumnName, 30) - 1)
from TableName

This will find the first space starting at position 30, and return everything to the left of that.

For more, see here: http://msdn.microsoft.com/en-us/library/ms186323.aspx

Upvotes: 1

Related Questions