Removing null characters from SQL char values

[Skee777]

I screwed up. I created a SQL table with char fields. Now when I try to post the data from the SQL table to an access table, it has null characters padding the data. I tried to trim the records, but the characters are not actually spaces. I tried to figure out how to use the replace function in vbscript, but have not had any luck. I have since changed the data type to varchar, but the existing data in the table still holds the padding.

Can anyone help.

 

[Tarwn]

Trim should work (it has for me in the past) because the padding is actually space padding. One possibility would be to try a replace before resinserting into your vachar field:

unpaddedStr = Replace(strFromDb,right(strFromDb,1),"")

This will only work once per record, after that it will mess them up. Basically it is taking the last character of the string (padding in this case) and replacing all occurrences of it with a null (in essence removing them).

I am still thinking that the padding is spaces, I had an edit tool at one point that was cleaning up entries I had changed fro mtext to varchar and all I did was trim them.

It might be a good idea to just go through the curent records and trim them by hand, thus solving the problem for all time.
-Tarwn "Customer Support is an art not a service" - marketing saying
"So are most other forms of torture" - programmers response
(The Wiz Biz - Rick Cook)

 

[Turkbear]

Since you have now changed them to Varchar you can use an update statement with substr to 'truncate' them..
Something like this:

Update table_name set char_now_varcharfield = 
  substr(char_now_varcharfield,1,Instr(char_now_varcharfield ,' ') -1);

This will basically use only the characters up to 1 before the first space - It assumes no actual spaces in the fields before the padding...

hth,