using trim in a select statement

I have a table, my_table, that has a field my_field. myfield开发者_高级运维 is defined as VARCHAR(7). When I do:

SELECT myfield 
  FROM my_table;

I get what appears to be the entire 7 characters, but I only want the actual data.

I tried:

SELECT TRIM(myfield)
  FROM my_table;

and several variations. But instead of getting 'abcd', I get 'abcd '.

How do I get rid of the trailing blanks?

---

As others have said:

• trim whitespace before data enters the database ("Mop the floor...);
• ensure this is not actually a column of type CHAR(7).

Additionally, add a CHECK constraint to ensure no trailing spaces ("...fix the leak.") While you are at it, also prevent leading spaces, double spaces and zero-length string e.g.

CREATE TABLE my_table
(
 myfield VARCHAR(7) NOT NULL    
    CONSTRAINT myfield__whitespace
       CHECK (
              NOT (
                   myfield = ''
                   OR myfield LIKE ' %'
                   OR myfield LIKE '% '
                   OR myfield LIKE '%  %'
                  )
             )
);-

---

VARCHAR columns will not pad the string you insert, meaning if you are getting 'ABCD   ', that's what you stored in the database. Trim your data before inserting it.

Make sure you are not using the CHAR datatype, which will pad your data in the way you suggest. In any case:

SELECT TRIM(myfield) FROM mytable; 

will work.

Make sure also that you are not confusing the way the SQL interpreter adds padding chars to format the data as a table with the actual response.

---

Make sure that you are not inserting data in this column from a CHAR(7) field.

---

You need to trim your result when selecting as opposed to when inserting, eg:

SELECT TRIM(myfield) FROM my_table;