SQL "left" function - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL "left" function

Author

Topic

overtmc
Starting Member

3 Posts

Posted - 2005-11-14 : 16:31:46

I am using the following to display a short version of my description field:

<%=LEFT(rs("DESCRIPTION"),150)%>

The only problem is that it cuts at exactly 150 chars...so it will display a portion of a word.

Can any one help me change this so it will cut the record off at the last "space" before the 150 chars is up? So it doesn't chop the last word.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-14 : 16:39:06

How do you know the last word is complete or not? Do you want to drop the last word regardless?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-14 : 16:43:58

[code]

DECLARE @x varchar(8000)
SELECT @x = 'The town of West Orange, NJ, has a very convenient service entitled “Midtown Direct Shuttle Bus Service”.'
+ ' The service has several Morning & evening runs with several stops in the Valley, Gregory and'
+ ' St. Clouds sections of town. For areas outside these sections, a park and ride has been established'
+ ' in the Valley at the corner of Valley Road and Main Street. Not only is the service a boon for an'
+ ' ever overcrowding Essex County, it is also the environmentally right thing to do. Add to the mix the'
+ ' fuel problems that we face today and you have a service that should grow to encompass the entire town,'
+ ' and add additional runs. It is such a popular service that all of the surronding towns also supply the'
+ ' same service.'

SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(@x,1,150)),CHARINDEX(' ',REVERSE(SUBSTRING(@x,1,150)))+1,150-CHARINDEX(' ',REVERSE(SUBSTRING(@x,1,150)))))

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-14 : 17:24:38

Hi-jacking your post...

DECLARE @x varchar(8000)
SELECT @x = 'The town of West Orange, NJ, has a very convenient service entitled “Midtown Direct Shuttle Bus Service”.'
	+ ' The service has several Morning & evening runs with several stops in the Valley, Gregory and'
	+ ' St. Clouds sections of town.  For areas outside these sections, a park and ride has been established'
	+ ' in the Valley at the corner of Valley Road and Main Street.  Not only is the service a boon for an'
	+ ' ever overcrowding Essex County, it is also the environmentally right thing to do.  Add to the mix the'
	+ ' fuel problems that we face today and you have a service that should grow to encompass the entire town,'
	+ ' and add additional runs.  It is such a popular service that all of the surronding towns also supply the'
	+ ' same service.'

SELECT left(@X, 150-CHARINDEX(' ', REVERSE(LEFT(@X, 150))))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources