Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

String Manipulation in DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Kumar Ashok

New User


Joined: 20 Jan 2007
Posts: 29
Location: KOLKATA

PostPosted: Thu Dec 20, 2007 9:01 am    Post subject: String Manipulation in DB2
Reply with quote

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
Back to top
View user's profile Send private message

abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Thu Dec 20, 2007 11:59 am    Post subject: Reply to: String Manipulation in DB2
Reply with quote

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.
Back to top
View user's profile Send private message
Kumar Ashok

New User


Joined: 20 Jan 2007
Posts: 29
Location: KOLKATA

PostPosted: Thu Dec 20, 2007 1:08 pm    Post subject:
Reply with quote

Thanks Abhishek,

Your help was quite a useful. It really helped me solved my problem.
Thanks again!!!
Back to top
View user's profile Send private message
karnataka

New User


Joined: 15 Sep 2006
Posts: 20
Location: bangalore

PostPosted: Thu Dec 20, 2007 2:22 pm    Post subject: Reply to: String Manipulation in DB2
Reply with quote

Hi,

Here is the query to eliminate Leading and trailing spaces

Code:
SELECT  LTRIM(RTRIM(column-name))  AS txt2   
FROM    table-name;


Here RTRIM eliminate trailing spaces
LTRIM eliminate leading spaces
*****************************************************************

Query to print data in Uppercase/Lowercase:-

Code:
SELECT                                 
LCASE(column-name) AS lname,
UCASE(Column-name) AS uname       
FROM   Table-name   


LCASE is to conver from Uppercase to lower-case and UCASE is vice versa

Thanks,[img][/img]
Back to top
View user's profile Send private message
Kumar Ashok

New User


Joined: 20 Jan 2007
Posts: 29
Location: KOLKATA

PostPosted: Thu Dec 20, 2007 2:33 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
arivazhagan_k

New User


Joined: 05 Dec 2007
Posts: 57
Location: chennai

PostPosted: Thu Dec 20, 2007 3:15 pm    Post subject:
Reply with quote

abhishek,

Quote:
SELECT UCASE(REPLACE(' FORT LEE ',' ','')) FROM SYSIBM.SYSDUMMY1;


Is Replace function will work on DATE field ?
For me it is not working .

Code:
SELECT DATE_FIELD  FROM QUALIFIER.TABLENAME ;

[u]OUTPUT[/u]
12/31/2015
09/07/2007
09/06/2007
If i query like below means

Code:
SELECT REPLACE(PARM_DATE,'/','-') FROM S07.NZ241 ;   


it will give error like
Code:
QUERY MESSAGES:                                         
 Argument '1' of scalar function 'REPLACE' is invalid.   
 *** END ***                                             
Back to top
View user's profile Send private message
Kumar Ashok

New User


Joined: 20 Jan 2007
Posts: 29
Location: KOLKATA

PostPosted: Thu Dec 20, 2007 3:23 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Thu Dec 20, 2007 3:24 pm    Post subject: Reply to: String Manipulation in DB2
Reply with quote

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.
Back to top
View user's profile Send private message
arivazhagan_k

New User


Joined: 05 Dec 2007
Posts: 57
Location: chennai

PostPosted: Thu Dec 20, 2007 4:33 pm    Post subject:
Reply with quote

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 ***
Back to top
View user's profile Send private message
arivazhagan_k

New User


Joined: 05 Dec 2007
Posts: 57
Location: chennai

PostPosted: Thu Dec 20, 2007 4:52 pm    Post subject:
Reply with quote

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

Code:
SELECT REPLACE(CHAR(DATE_FIELD),'/','-') FROM QUALIFIER.TNAME ;

----------
2015-12-31
2007-09-07
2007-09-06
2007-09-05
Back to top
View user's profile Send private message
kalamusu

New User


Joined: 20 Dec 2007
Posts: 1
Location: Hyderabad

PostPosted: Thu Dec 20, 2007 9:50 pm    Post subject:
Reply with quote

Thank you for the information.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Replacing same string with different ... vickey_dw DFSORT/ICETOOL 6 Wed Feb 22, 2017 10:44 pm
No new posts Count Trailing Spaces in variable str... Virendra Shambharkar SYNCSORT 10 Thu Feb 02, 2017 12:23 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Random Password (in string format) ge... ezhavendhan COBOL Programming 10 Mon Aug 29, 2016 3:18 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us