Re: Remove a character from a string (regardless position)

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.