Trimming a field

[simonB2020]

I am using a File source.
Column in question is defined in Replicate a WSTRING(255)

Target is Redshift
Also defined in Replicate as WSTRING(255), and in Redshift itself as varchar(765)

Yet when the load executes, Redshift is throwing an error.
'String length exceeds DDL length'

Now, I can see in the source file, that there are extra spaces added onto the end of a value for this column , which explains where the extra length is.
However ....

If I am telling Replicate that the source is 255, shouldn't it either  throw an error when it reads the source file, or truncate the value before passing it to Redshift ?
Else what was the point of telling Replicate that the source fields is any length at all ?

I am just surprised as to why the extra length is getting to Redshift at all ?

Thanks

[john_wang]

Hello @simonB2020 ,

Not very sure why it's defined as WSTRING(255) - it means NCHAR(255) in RDBMS. Anyway, you can use length() function to figure out the real length of the string. If it's padded by spaces, you may use trim() function to remove the unnecessary spaces, like:

This can be set in column level (table settings), or in Global Transformation Rules of task "Global Rules...".

Hope this helps.

Regards,

John.

[Bill_Steinagle · Accepted solution]

Hi,

Thank you for your post to the Replicate Forums. Along with John's update here is the mapping of the WSTRING to the Redshift Data Type and the size.  This may explain why there is extra bytes on the Redshift Target.

WSTRING

If length is => 1 and =< 65535, then: NVARCHAR (Length in Bytes) If length is => 65536 and =< 2147483647, then: NVARCHAR (65535)

Thanks!

Bill