[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