To remove the space in the string
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 31
- Joined: Tue Jul 13, 2004 5:26 am
- Location: chennai
- Contact:
To remove the space in the string
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
ex. input col: AB IT
req output col:ABIT
Lakshmi
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
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
----------------
Rgds,
Anupam
----------------
The future is not something we enter. The future is something we create.
Rgds,
Anupam
----------------
The future is not something we enter. The future is something we create.
-
- Participant
- Posts: 31
- Joined: Tue Jul 13, 2004 5:26 am
- Location: chennai
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.ordepending on whether you're working through ODBC or not.
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;
Code: Select all
SELECT CONCAT(SUBSTR(Product,1,2),SUBSTR(Product,4,2)) FROM tablename;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: