Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL update query to remove spaces

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
like image 443
andreas Avatar asked Mar 02 '10 13:03

andreas


People also ask

How do I remove a space in MySQL?

The TRIM() function returns a string that has unwanted characters removed. Note that to remove the leading spaces from a string, you use the LTRIM() function. And to remove trailing spaces from a string, you use the RTRIM() function.

How do I remove all spaces from a string in MySQL?

Remove all the whitespaces from the entire column values We will be using the replace() function as replace() function will remove the white spaces from between, start, and the end of the string value.

How do I remove spaces between names in SQL?

SQL Server TRIM() Function The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string.


1 Answers

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);
like image 74
Daniel Vassallo Avatar answered Oct 19 '22 17:10

Daniel Vassallo