Abbreviate only part of a string with a CASE statement?

[Idokyoku2]

Good Morning,
I'm trying to save time, hopefully cutting down the usual 5-8 hours of editing in VFP, using a CASE statement to edit obscenly long columns for Piggy-Back label purposes. My reasoning is we are limited between 30-35 characters.

I've tried this SQL but it's obviously changing all data within the column matching the "THEN" clause.

Select Name, Title =
CASE
WHEN TILE LIKE '%President% THEN 'Pres.'
ELSE TITLE
END
From TBL1;

Title is currently - 'Senior Vice President for Media and Public Education'.

Target is - 'Sr. V.P. for Mda & Pblc. Ed.'

Any suggestions?

David

 

[SQLSister]

This is a forum for microsoft SQL Server not a VFP forum, so you should take the question there.

However, my suggestion would be to add a column for the short name and put the abbreviuted name in it. To do this on the fly whenever you do a query will be very inefficient.

I don;t know if VFP has a replace function, but I would use it to update the short title column I suggested with teh standard abbreviations. Of course if you are never going to use the long titles, then don't allow users to enter them.

Questions about posting. See faq183-874

 

[Idokyoku2]

I'm using SQL Server 7, but edit generally with VFP as it seems to be more efficient for this type of need. However, there are thousands of records of which I thought a programmatic short-cut may come in handy.

So, my understanding is there really isn't one with SQL, short of righting the most lenghty CASE statement in history?

I do appreciate your timely reply.
David