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
 

 

Count of characters

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

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Tue Dec 29, 2009 4:05 pm    Post subject: Count of characters
Reply with quote

Hi,

My requirement is as below:

I have a query which returns the name of employees, I need to change it in such a way that, there should be a column added to the query which returns the no. of charecter "A" in it.

Current result:

Name

Alpha
Beta
Gamma


Expected Results

Name A_count

Alpha 2
Beta 1
Gamma 2

Is this possible?
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Tue Dec 29, 2009 4:10 pm    Post subject:
Reply with quote

Requirement - a serious business requirement.
Are you sure that you should not have posted this in the "Interview Questions" forum.
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Tue Dec 29, 2009 4:24 pm    Post subject:
Reply with quote

Expat,

icon_confused.gif icon_confused.gif This not a question asked for any interviews.We are trying to develop an Portal, for which we have encountered such a requirement. Just wanted to make the understandability of my question easy, and hence given such a scenario. icon_eek.gif
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Dec 29, 2009 5:36 pm    Post subject:
Reply with quote

Ajesh,
You can do it using a recursive query as below.

Code:
WITH CNTCTE (Name, FIND_LAST, REMAINDER) AS
(SELECT Name
,LOCATE('A',UPPER(Name))
,SUBSTR(Name,LOCATE('A',UPPER(Name)) + 1)
FROM YOUR_TABLE
WHERE UPPER(Name) LIKE '%A%'
UNION ALL
SELECT Name
,FIND_LAST + LOCATE('A',UPPER(REMAINDER))
,SUBSTR(REMAINDER,LOCATE('A',UPPER(REMAINDER)) + 1)
FROM CNTCTE
WHERE UPPER(REMAINDER) LIKE '%A%'
)
SELECT Name,COUNT(Name) AS COUNT_A
FROM CNTCTE
GROUP BY Name
ORDER BY Name;
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10256
Location: italy

PostPosted: Tue Dec 29, 2009 7:36 pm    Post subject: Reply to: Count of characters
Reply with quote

since You are talking about employee names,
the info is not so volatile to have to recomputed every time.
I would evaluate the possibility of storing it as a column at row update/insert time
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 Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts Display FTP Session Count within TSO Yolanda Harvey TSO/ISPF 3 Fri Mar 10, 2017 10:31 pm
No new posts Count Trailing Spaces in variable str... Virendra Shambharkar SYNCSORT 10 Thu Feb 02, 2017 12:23 pm


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