× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Peter -

If FNAME has no embedded blanks, this will work (the LOCATE scalar function can be used to find the first occurence of a blank):

Select
SUBSTR(FNAME,1,(LOCATE(' ',FNAME) - 2)) from YOURFILE

I'll leave it to you to figure out what to do if there are no trailing blanks in the field...

HINT: you'll need to use a CASE statement in your SQL to check if LOCATE = 0, this means no blank was found.
In this case, your substring will be SUBSTR(FNAME,1,9)

(Sorry, but I'm about to hit the road for Austin...)

Regards,
Steve

----- Original Message ----- From: "Peter Vidal" <Peter_Vidal@xxxxxxxx>
To: <midrange-l@xxxxxxxxxxxx>
Sent: Friday, July 27, 2007 2:55 PM
Subject: SQL: Remove a character from a string (regardless position)


Hi list!

I have a file (with a couple of thousand records in it) that has a field
called FNAME (10/A) and I want to be able to remove the last character of
the value. I may have:

FILAX
TEMPERED
IGLANDT

So after the execution of the SQL, I want to have:

FILA
TEMPERE
IGLAND

How I do that in SQL?

Thanks!


Peter Vidal
MAPICS IT Technical Support Team / SR System Analyst
10540 Ridge Rd., Ste 203, New Port Richey, FL 34654-5111
http://www.pall.com

"The wisest mind has something yet to learn."
George Santayana (1863 - 1952)

--------------------------------------------------------------------------------

Attention:

This communication may contain information that is confidential,
privileged and/or exempt from disclosure under applicable law.
If you are not the intended recipient, please notify the sender
immediately and delete the original, all attachments, and all
copies of this communication.


--------------------------------------------------------------------------------



--------------------------------------------------------------------------------


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.