trimming spaces of varchar fields

[aartlett]

G'day,
I'm having an issue where varchar fields with direct mapping are trimming leading, trailing and internal double spaces ... sometimes. I write sometimes because it doesn't always happen.

Here's some of the suss data:

Code: Select all

CONTACT1_NAME|contact_name
 KAY -VICTORIA PAM -KAVANR|KAY -VICTORIA PAM -KAVANR
MR  PAUL COOPER|MR PAUL COOPER
MRS BEVERLEY  FRIEDMAN	|MRS BEVERLEY FRIEDMAN
MR PAUL        / CASTLE SCHOENAVER  / RACHEAL|MR PAUL / CASTLE SCHOENAVER / RACHEAL
 JANINE ROISSETTER|JANINE ROISSETTER

I hope you get the idea. I also have some names with 2 spaces that are converted to 1 space ... again sometimes.

Job logic is:
ODBC Enterprise ->
TRF ->
some lookups ->
Chksum ->
TRF ->
ODB Connector

the contact1_name field is mapped to contact_name in the first transformer directly with no changes and then carried through without change.

Contact1_name is varchar(180), contact_name is varchar(255).

I'd just write it off as things happen, except they don't all the time.

Things to try, look at or investigate would be greatly appreciated.

[ray.wurlod]

Do some have Char(32) spaces and some Char(0) pad characters?

[aartlett]

Ray, I don't know. I'll check that on Monday. Thanks for the ideas. I've found some char(126) quote marks, so strange spaces wouldn't be unusual.

Do you have an idea why it would be doing a full trim though?

[ray.wurlod]

Not really. I've seen that before when someone inadvertently used Convert() to eliminate all spaces, but will choose to believe that you have not done that.

[aartlett]

Honest injuns, there is a direct copy in the transforms. no formulae, no functions.