In reviewing the data in one of my SQL Server tables, I noticed that my nchar or nvarchar fields have additional spaces added to the end of the record. Is there a way to stop this? Should I be using a different data type?
If the field is of variable length then use nvarchar. Just make sure to do a Trim on the text when you patch in the parameter. What goes into the field is exactly what you tell it to. The spaces are there because they were in the original text field.
Just as a side note in case this happens again and trim doesn't work. Example, yesterday I was dealing with what looked like white spaces at the end of each string in on of my tables. Therewere two spaces and the table accepted them as white spaces but in fact they were Extended ASCII characters. RTRIM would not remove them and another guru and I spent hours trying to figure out what they were and how to remove them. We had to settle on doing a REPLACE on the strings where the ASCII characters were greater than 54. The data was from a website and no matter what we couldn't see them as characters until we did and ASCII check. So in saying this, if there is ever an issue where the trailing spaces won't trim, run an ASCII check on the last characters and replace them with "".
I can post the exact statement I used later if it is wanted.
Rev. Jim, good point..... I'm working fast and hard to catch up!! I'm such a rookie.... It won't take long!
Stuugie, I was able to get it fixed with no issues. However if you will share your code, I know I'll need it down the road... I've still got multiple tables to build and populate. I've got to believe it will come in handy.
Thanks group. I've gotten a long way as a result of your mentoring...... especially you, Jim.
Sorry for the delat but I have been up to my eyeballs with exam studying and not working much. Anyhow, I'm at work now and the following SQL statement removes unwanted, extended, ascii characters where the ascii number is greater than 57 (which is the number 9). Obviously the field names would have to be changed to suit specific situations.
--This only removes one ascii char at a time and may have to be run more than once.
set vSeries_Number = LEFT(tblVSeriesList.vSeries_Number,LEN(tblVSeriesList.vSeries_Number)-1)