Search code examples
sqlms-accesstrimdata-cleaning

LTRIM() returns cells with "-1"

I have a set of rows that has a space entry by itself (" ") instead of being empty ("") and want to remove it.

I tried using LTRIM(field), RTRIM(field), LTRIM(RTRIM(field)), RTRIM(LTRIM(field)), and TRIM(field) - all of which replaces the cell with a "-1":

ID  | Location | Name 
--- | -------- | ---
 1  | A        | " "
 2  | B        | " "
 3  | C        | ""

Result (using trim)
ID  | Location | Name 
--- | -------- | ---
 1  | A        | -1
 2  | B        | -1
 3  | C        | ""

This seems like a simple operation, but I can't get it to remove the space. Can anyone please help?

Solution

  • Trim (Rtrim o Ltrim) are for removing space at the margin of the string .. in your sample seems you want remove space inside a string in this case use replace

    select id, location , Replace(Name, " ", "")
from your_table