|
View previous topic :: View next topic
|
| Author |
Message |
ajeshrn
New User
Joined: 25 Mar 2009
Posts: 78
Location: India
|
|
|
|
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 |
|
 |
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
Requirement - a serious business requirement.
Are you sure that you should not have posted this in the "Interview Questions" forum. |
|
| Back to top |
|
|
ajeshrn
New User
Joined: 25 Mar 2009
Posts: 78
Location: India
|
|
|
|
Expat,
 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.  |
|
| Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
|
|
