1

I have data in DB2 then i want to insert that data to SQL.

The DB2 data that i had is like :

select char('AAA   ') as test from Table_1

But then, when i select in SQL after doing insert, the data become like this.

select test from Table_1

result :

test
------
AAA

why Space character read into box character. How do I fix this so that the space character is read into.

Or is there a setting I need to change? or do I have to use a parameter?

I used AS400 and datastage.

Thank you.

Barett
  • 5,826
  • 6
  • 51
  • 55
fushia03
  • 11
  • 1
  • What do you mean by "insert that data to SQL"? Is your destination database in SQL Server? – Kaf Jun 26 '14 at 08:08
  • You could do a simple update on SQL Server to replace box char with space. `UPDATE Table_1 SET test = REPLACE(test, '', ' ')` – Kaf Jun 26 '14 at 08:31

2 Answers2

1

Datastage appends pad characters so you know that there are spaces there. The pad character is 0x00 (NUL) by default and that's what you're seeing.

Research the APT_STRING_PADCHAR environment variable; you can set it to something else if you want.

The 0x00 characters are not actually in your database. The short answer is, you can safely ignore it.

Barett
  • 5,826
  • 6
  • 51
  • 55
0

When you said:

select char('AAA   ') as test from Table_1

You were not actually showing any data from the table. Instead you were showing an expression casting a constant AAA as a character value, and giving that result column the name test which coincidentally seems to be the name of a column in the table, although that coincidence doesn't matter here.

Then your 2nd statement does show the contents of the database column. 

select test from Table_1

Find out what the hexadecimal value actually is.

WarrenT
  • 4,502
  • 19
  • 27