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

Casey Crookston caseyc at IntelliSoftmn.com
Mon Aug 7 09:56:00 CDT 2006


Here's a complete description of my problem.  I've been given a databse
of headings for a phone book, but both the main headings and the sub
headings are lumped into one table and seperated with a dash. Some
examples:

Headings
--------------------------------
Accountants
Accountants-Certified Public
Accountants-Licensed Public

Should really be:

Main_Headings		Sub_Headings
-------------------------------------------------
Accountants
Accountants			Certified Public
Accountants			Licensed Public

That in and of itself is a problem I can handle (although I will say it
took me a long time to figure it out.)  Here's how I tackled it: (please
don't laugh!)

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

BUT, the trouble is, the wonderful person who complied these headings
sometimes used a dash not as a delimiter, but as part of the heading
title.  Some Examples:

Headings
--------------------------------------------------------
Appliances-Major-Used-	
Appliances-Major-Parts & Supplies

Needs to become:

Main_Headings		Sub_Headings
-------------------------------------------------
Appliances			Major-Used
Appliances			Major-Parts & Supplies

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

Thanks!




More information about the thelist mailing list