String Manipulation in DB2

Kumar Ashok · New User Joined: 20 Jan 2007 Posts: 29 Location: KOLKATA

Hi All,

Requirement goes like -

let us consider the data in a Table as - bbbfortbbleebb

"b" represents SAPCES or LOW-VALUES.

when we fetch this data the output should look like FORTLEE

Thus the requirement is
1) To do string manipulation while doing SELECT so that all the leading/trailing/in between SPACES/LOW VALUES are removed.
2) To change all the lowercase data to uppercase. I know the command UCASE but how to use it i am not aware.

Could anyone please help.

Thanks

abhishekmdwivedi · New User Joined: 22 Aug 2006 Posts: 95 Location: india

Hi Ashok,

This is pretty simple query , have a look at the below statement:
SELECT UCASE(REPLACE(' FORT LEE ',' ','')) FROM SYSIBM.SYSDUMMY1;

I think this is what you require.

Kumar Ashok · New User Joined: 20 Jan 2007 Posts: 29 Location: KOLKATA

Thanks Abhishek,

Your help was quite a useful. It really helped me solved my problem.
Thanks again!!!

karnataka · New User Joined: 15 Sep 2006 Posts: 20 Location: bangalore

Hi,

Here is the query to eliminate Leading and trailing spaces

Kumar Ashok · New User Joined: 20 Jan 2007 Posts: 29 Location: KOLKATA

Hi Karnataka (Sorry Don't know ur name)

Your information is helpful if the leading or trailing spaces are to be removed but this will not help in removing in between spaces, as per I know. Abhishek's suggestion works well for that condition also. But yes thanks for your help and response.

Regards

arivazhagan_k · New User Joined: 05 Dec 2007 Posts: 57 Location: chennai

abhishek,

Kumar Ashok · New User Joined: 20 Jan 2007 Posts: 29 Location: KOLKATA

Hi Ari,

FYI..

REPLACE

+------------------------------------------------------------------------+
| |
| >>--REPLACE(source-string,search-string,replace-string)------------->< |
| |
+------------------------------------------------------------------------+

The schema is SYSIBM.

The REPLACE function replaces all occurrences of search-string in
source-string with replace-string. If search-string is not found in
source-string, source-string is returned unchanged.

source-string
An expression that specifies the source string. The expression must
return a value that is a built-in character or graphic string data
type that is not a LOB, and it cannot be an empty string.

So it seems to me it works with the fields whose data type is defined as CHAR and nothing else.

Thanks

abhishekmdwivedi · New User Joined: 22 Aug 2006 Posts: 95 Location: india

arivazhagan,

You have to convert the date field into characters using TO_CHAR scaler function and then apply the REPLACE. This will do the trick.

arivazhagan_k · New User Joined: 05 Dec 2007 Posts: 57 Location: chennai

TO_CHAR function is also having some error . Is this due to DB2 version or something else ?

SQL QUERY MODIFIED LINE 1

SELECT TO_CHAR(PARM_DATE) FROM S07.NZ241 ;

QUERY MESSAGES:
SQL error at or before ( (line 1, position 15).
*** END ***

arivazhagan_k · New User Joined: 05 Dec 2007 Posts: 57 Location: chennai

This query is working for me . Thanks for ur reply.

kalamusu · New User Joined: 20 Dec 2007 Posts: 1 Location: Hyderabad

Thank you for the information.