Group,
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?
In advance, thanks for your responses.
Don
Jump to PostIf 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.
Jump to PostJust execute the following query
UPDATE mytable SET fld1 = RTRIM(fld1), fld2 = RTRIM(fld2)...with the names of the fields you want to modify
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.
Rev. Jim, I've tried to remove these spaces, but they won't go away. Is this normal? I'll ensure that the data type is nvarchar.
Thanks,
Don
Just execute the following query
UPDATE mytable SET fld1 = RTRIM(fld1), fld2 = RTRIM(fld2)...
with the names of the fields you want to modify
Rev. Jim.....
Outstanding.... it worked perfectly....
By the way... I did change the nchar to nvarchar for those fields that would vary in character number.
Now...... how did you get so smart!!
Thanks for your help again.
Don
how did you get so smart
I've just been at it long enough to make a lot more mistakes than you ;-P
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.
Don
I am not in front of my code right now but will be on Friday and I'll post it up then.
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.
update tblVSeriesList
set vSeries_Number = LEFT(tblVSeriesList.vSeries_Number,LEN(tblVSeriesList.vSeries_Number)-1)
where ASCII(right(tblVSeriesList.vSeries_Number,1))>57
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.