SQL delete last space (if exist) in column-CodePudding

I have a database with a customer table. In this table there is, among other things, a column with email addresses (and other data). I searched for incorrectly entered email addresses, the application does not validate the entered data.

SELECT KnA_GIDNumer,KnA_EMail
FROM CDN.KntAdresy ka  
WHERE CHARINDEX(' ',KnA_EMail) > 0;

Output:

46  [email protected]  
86  [email protected] 
139 [email protected]

How I can remove last space from email address?

CodePudding user response：

Maybe using this:

update CDN.KntAdresy
set KnA_EMail = REPLACE(KnA_EMail, ' ', '')
where CHARINDEX(' ',KnA_EMail) > 0;

CodePudding user response：

UPDATE CDN.KntAdresy SET KnA_EMail = TRIM(KnA_EMail)

This will remove spaces in front and from the back of the field.

CodePudding user response：

I use:

update CDN.KntAdresy
set KnA_EMail = TRIM(KnA_EMail)

work fine, thanks.

CodePudding user response：

In case you want to remove the last spaces only, then use:

UPDATE CDN.KntAdresy
SET KnA_EMail = RTRIM(KnA_EMail);

And if you want to remove the spaces from both sides, then:

UPDATE CDN.KntAdresy
SET KnA_EMail = TRIM(KnA_EMail);