[thelist] SQL Question - Drop the last charector of a string

[Casey Crookston]

Ok Jason.  Thanks!  That makes sense.

I created a new table called temp_headings and ran this query:

insert into temp_headings
select distinct 
replace(left(heading + '-', charindex('-', heading + '-')), '-', '') AS
heading,
replace(right(heading + '-', len(heading + '-') - charindex('-', heading
+ '-')), '-', '-') AS sub_heading
from listings
order by heading

temp_headings is now populated.  Then I tried this:

insert into headings
select 
main_heading,
LEFT(sub_heading, LEN(sub_heading) - 1) AS sub_heading
from temp_headings
order by main_heading, sub_heading

But I receive this error in Query Manager: 

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

For some reason MS SQL does not seem to like the -1.  Any idea why?  Or
am I looking at the wrong place for the error?

Thanks!!!!

Casey


-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Rob Smith
Sent: Monday, August 07, 2006 10:02 AM
To: thelist at lists.evolt.org
Subject: Re: [thelist] SQL Question - Drop the last charector of a
string

>I am really having fits over all of this.  ANY help would be
fantastic!!!!

I'd probably run two passes through this database table, it might make
it easier. The first pass would be to separate <heading>-<subheading
with anything -and I - mean anything-> into the sub heading field. The
second pass would be to do the left(MyField,len(MyField)-1) trick. 



Rob Smith
LexJet
rob.smith at lexjet.com
http://www.lexjet.com
(800)453-9538
(941)330-1210 Int'l
(941)330-1220 Fax
1680 Fruitville Road, 3rd Floor
Sarasota, FL 34236

-- 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt !