Trim a column in a SQL statement

I am trying to write a SQL statement that will trim the last characters after a whitespace in one of the columns called Postcode. Any ideas how I do this. Here is the code so far
SELECT blabla.`User`.UserName as USER, blabla.`User`.Postcode AS pcode, blabla.`User`.Tel, blabla.`User`.Email, blabla.`User`.Address, blabla.Customer.CustomerID FROM blabla.Customer, blabla.`User` WHERE blabla.Customer.UserID = blabla.`User`.UserID AND blabla.`User`.Postcode IS NOT NULL"
Customer has a PK CustomerID and a FK UserID. User has a PK UserID
The data is from a MySQL database
Seb
sebastizAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
you can use RIGHT Function.
0
Renante EnteraSenior PHP DeveloperCommented:
Hi sebastiz!

I think this is what you are looking for :
===========================================
SELECT
  blabla.`User`.UserName as USER,
  blabla.`User`.Postcode AS pcode_orig,
  LEFT(blabla.`User`.Postcode, LENGTH(blabla.`User`.Postcode) - LOCATE(' ', REVERSE(blabla.`User`.Postcode))) as pcode_trim,
  blabla.`User`.Tel, blabla.`User`.Email, blabla.`User`.Address, blabla.Customer.CustomerID
FROM blabla.Customer, blabla.`User`
WHERE blabla.Customer.UserID = blabla.`User`.UserID
AND blabla.`User`.Postcode IS NOT NULL"
===========================================

So, the logic there is that you will locate first the position of the first occurence of "white space" but be sure to reverse the actual string.  Once you got the position then that would be deducted to the length of the actual string.  So, you will simply use function LEFT() to comeup with the expected value.

I hope that this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Renante EnteraSenior PHP DeveloperCommented:
Hi sebastiz!

I'm glad that I have helped you.  Hope to help you again...

BTW, thanks for the points and grade.


Regards!
eNTRANCE2002 :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.