To remove the space in the string

[lakshmipriya]

How can i achieve the output of the string with its space in between being removed in DB2 stage written in DB2 as a query

ex. input col: AB IT

req output col:ABIT

[richdhan]

Hi Lakshmi,

Check Trim(input col, " ", "A") works.

--Rich

[anupam]

To add
Trim(input col, " ", "A")
First argument is the input data
2nd argument is the character which is space in your case.
3rd argument is the option which is "A" which means ALL

[lakshmipriya]

i want to make this trim inside the DB2 query in a DB2 API stage itself could u pls let u know more exact query for writing the same

[anupam]

I have not worked on DB2 but i think if you use tim(inputCol) function, it will remove all the spaces.

Try using it, it should work.

[ray.wurlod]

Check out the DB2 help on TRIM - it may only remove "extraneous" spaces (leaving a single space).
In this case, and if the pieces either side of the space are of known size, you could use substring and concatenation techniques.

Code: Select all

SELECT SUBSTRING(Product FROM 1 FOR 2) || SUBSTRING(Product FROM 4 FOR 2) FROM tablename;

or

Code: Select all

SELECT CONCAT(SUBSTR(Product,1,2),SUBSTR(Product,4,2)) FROM tablename;

depending on whether you're working through ODBC or not.

[vmcburney]

How about the DB2 REPLACE command.

REPLACE(Product, ' ', '')

I also suggest that you don't use user defined SQL, instead stick to generated SQL and put the REPLACE command into the Derivation field on your columns tab, you will then see it turn up in your generated SQL tab. Generated SQL is usually easier to maintain over time then user defined SQL as it is easy to add and remove columns. You can then save the column definition with the REPLACE statements in your saved table definition for use by other jobs.

[ray.wurlod]

It's a good day, I've learned something new. Yay!

[mandyli]

In DB2 itself " REPLACE" is there please replace .