CODEWITHSUNDEEP
sqlsql-serverreplaceascii
KAT
KAT

Reputation: 33

SQL -how to remove all ASCII char (blanks after field entry)

I have spent way to much time trying to figure this out.... looking a dozens of other answers.

I have table in SQL Server with a column of type Char(32) NULL. All items in the table column are only char(9), but I have blanks in the remaining spots (when running select ascII(right(myField, 1)) there is a 32 in there).

I have tried a replace, tried to update the field from a tempTable tried to delete and update from tempTbl..... everytime I select... the fields are still 30 in length.

Is there a way to remove all of the extra spaces? or is this just the way the CHAR fields always work?

I have tried:

UPDATE table  
SET myfield = rtrim(replace(myField , char(160), char(32)))

UPDATE mytable
SET myField  = REPLACE(RTRIM(LTRIM(myField )), CHAR(32), '')

Upvotes: 3

Views: 1411

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You must distinguish between

  • strings with fixed width and
  • strings with variable width.

In your case you are dealing with a fixed width. That means, that the string is always padded to its defined length. Fixed-width-strings live together with datetime or int values within the row.

If you define your column as VARCHAR(32) (meaning variable characters), the "32" is just limiting the max length. These values are stored (in most cases) somewhere outside of the row's storage space, while there's only a pointer to the actual storage place within the row.

Fixed lenghts are slightly faster then variable strings. But in most cases I'd advise to prefer the VARCHAR(x).

Check this out:

DECLARE @fix CHAR(32)='test';
DECLARE @variable VARCHAR(32)='test';

SELECT LEN(@fix),DATALENGTH(@fix)
      ,LEN(@variable),DATALENGTH(@variable)

Which results in 

4,32,4,4
  • The LEN()-function does not count the trailing spaces
  • The DATALENGTH()-function gives you the space actually used.

Upvotes: 3

Ravi
Ravi

Reputation: 1

char(32) will occupy all 32 characters.

If you want it to be trimmed and stored, you should use varchar(32).

Try converting your field to varchar.

Upvotes: 0

Related Questions