tkim90 tkim90 - 3 months ago 10
SQL Question

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?

Answer

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
Comments