0

I have a SQL Server database, when strings are saved they have a lot of white space.

Is there any way to remove them in the database, by updating the table, or when new information is entered?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
BatshevaRich
  • 550
  • 7
  • 19
  • 2
    Why would you need to redesign the table? Just do "update your_table set your_col = trim(your_col)" to remove the whitespace (I assume "white text" means whitespace). And to prevent it from happening in the future, you could modify your insert procedure to do a trim operation on the input. – dcp Jun 22 '20 at 17:50
  • Using compression while reduce the size if the white spaces are in the middle of the data. You would need to uncompress when reading. – jdweng Jun 22 '20 at 17:58
  • 2
    Whats the datatype? If you are using nvarchar, you should not have such a problem. – Legacy Code Jun 22 '20 at 17:59
  • @LegacyCode Perfect! Thanks to you, I can delete all my 'trimFromDB' functions... post as an answer so I can upvote it. – BatshevaRich Jun 22 '20 at 18:03
  • 1
    If open to a helper function... Take a peek at https://stackoverflow.com/questions/43148767/sql-server-remove-all-non-printable-ascii-characters/43149139#43149139 – John Cappelletti Jun 22 '20 at 18:13
  • I presume from comments above that you were using a fixed width datatype such as `nchar` or `char`? – Martin Smith Jun 22 '20 at 18:35

1 Answers1

1

:) Change your data type to NVARCHAR

Legacy Code
  • 650
  • 6
  • 10