MYSQL update query to remove spaces

Question

One of my clients has added a number of account numbers in one of our applications. While trying to make a transaction the transaction fails due to the spaces at the end of the account number. How do i update his records in the Mysql database to remove all the spaces from accounts that have them at the end, without making him delete the clients and re-adding the accounts? the structure of the table(s) is as follows:

Not sure how to structure the query or the function of the mysql

The account table:

the account table:
CUSTOMER_ID              
ACCOUNTNUMBER        
TXT                   
CURRENCY_NO            
USER_ID                  
ACTIVE_FLAG               
USER_DATE                 
ben_bic_address          
int_bic_address 

the admin table

  ADM_USER_ID           
  LOCATION_CD          
  LANG                
  USER_NAME              
  USER_LOGIN            
  USER_PASSWORD          
 GROUP_CODE            
 USER_ID              
  USER_DATE               
  ACTIVE                 
 COUNTER                
 connected              
 IP

And the customer table:

CUSTOMER_ID               
COUNTRY_NO              
USER_ID                   
CUSTOMER_NAME 
ACTIVE_FLAG

Refer this: http://stackoverflow.com/questions/281724/does-the-mysql-trim-function-not-trim-line-breaks-or-carriage-returns — Saurabh R S, Jun 04 '12 at 18:38

Daniel Vassallo · Accepted Answer · 2010-03-02T13:57:07.677

31

If you need to RTRIM() all the accounts of a particular customer, you can use a JOIN with your UPDATE statement as follows:

UPDATE
    accounts_table
INNER JOIN
    customers_table ON (accounts_table.user_id = customers_table.user_id)
SET 
    accountnumber = RTRIM(accountnumber)
WHERE
    customers_table.customer_id = 'customer id';

If you do not have many records in accounts_table, and you want to make sure that all the accountnumber values are trimmed, you can simply apply the trim to all the records as follows:

UPDATE
    accounts_table
SET 
    accountnumber = TRIM(accountnumber);

edited Mar 02 '10 at 13:57

answered Mar 02 '10 at 13:41

Daniel Vassallo

337,827
72
505
443

THANKS i tried that but i get the following error Error Code : 1305 FUNCTION abc1234.TRIM does not exist – andreas Mar 02 '10 at 13:45
1

@andreas: Make sure you do not have a space between the `TRIM` and the parenthesis `(`. It should be `TRIM(field)` not `TRIM (field)`. – Daniel Vassallo Mar 02 '10 at 13:53

Layke · Answer 2 · 2010-03-02T13:45:55.327

7

You would use TRIM and update.

Just using this should do it.

UPDATE accountTable
SET ACCOUNTNUMBER = RTrim(ACCOUNTNUMBER)

edited Mar 02 '10 at 13:45

answered Mar 02 '10 at 13:40

Layke

51,422
11
85
111

THANKS i tried that but i get the following error Error Code : 1305 FUNCTION abc1234.TRIM does not exist – – andreas Mar 02 '10 at 13:48
Take a look at this for ways to fix: http://bugs.mysql.com/bug.php?id=9051 You basically need to make sure you are using the correct cases. – Layke Mar 02 '10 at 13:49

score 0 · Answer 3 · answered Mar 02 '10 at 13:42

0

If you have foreign key constraints, then you may have to remove them whilst you make the modifications.

The following query will change the records in the accounts table:

update accounts set accountnumber = rtrim(accountnumber);

answered Mar 02 '10 at 13:42

a'r

35,921
7
66
67

score 0 · Answer 4 · answered Oct 12 '19 at 06:20

0

Try this

TRIM() Function removes extra (or) additional spaces from a string.

UPDATE item_listing SET product_quantity = TRIM(product_quantity);

Result:

Product_quantity = " 50 " => 50

answered Oct 12 '19 at 06:20

Solomon Suraj

1,162
8
8

MYSQL update query to remove spaces

4 Answers4

Linked