question

nevada1978 avatar image
nevada1978 asked

How to remove spaces from a field in sql

I have a table, xyzcodes (layout given below in the second section). I need to update the code column. As I discovered lately, a lot of the values have leading spaces. Examples given below. I need a code that will find the number of spaces in front of a value and then remove them. I have looked around and found stuff on LTRIM. Does it matter that the number of spaces varies from 1 to 5? Code 123 (3 spaces in front) 1234 (2 spaces in front) 12345 (1 space in front) state (char, 2) codetype (char, 2) code (char, 6) codetitle (varchar, 115)
updatessmstrim
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered
No, it does not matter: select ltrim(Code) as LeftTrimmedCode from yourTable will remove the leading spaces, no matter how many are there, but will not remove the trailing spaces, and actually, will add them to the right. Usually, to remove the trailing spaces it is possible to use RTRIM but in your case, applying RTRIM to Code column will accomplish nothing because the column is defined as char(6). Furthermore, if you remove the leading spaces from Code via LTRIM, these spaces will appear as trailing spaces now. This is because the column is defined as char(6) rather than varchar(6). CHAR values are always padded on the right with as many trailing spaces as necessary until the value is the same length (6 in your case). This behaviour does not apply to VARCHAR columns, so select ltrim(rtrim(codetitle)) codetitleTrimmed from yourTable will remove both leading and trailing spaces because the column is varchar, not char. I hope this explanation is not too confusing. If you need to remove all spaces from Code then you can do this: select ltrim(rtrim(cast(Code as varchar(6)))) TrimmedCode from yourTable; The script above will remove all spaces. Unfortunately, you will not be able to update the Code values so that they don't have the trailing spaces (the column is CHAR, it is always padded). Hope this helps. Oleg
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nevada1978 avatar image nevada1978 commented ·
@Oleg, Thanks for your assistance. The explanation makes perfect sense.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
@nevada1978 Please mark the answer as accepted if it helped you. Thanks.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.