Finding string within a string in a SQL statement

I have a column that shows the title of projects our company has gone through, another column has hours worked for each project.

The project titles contain keywords, keywords are defined by the format 'keyword:' i.e. 'ETL:'

Some projects have multiple keywords i.e. 'Client: ETL: ASCX: '

So for example, a project title could be 'Client: ETL: ASCX: update the import process'

I don't know the keywords ahead of time.

I want the total number of hours, and projects, for a given keyword So let's use the following two project titles as an example:

• Client: ETL: ASCX: had 20 hours of work put into it.
• Client: ETL: Bridge: had 10 hours of work put into it.

The report should give:

Keyword - Total Projects - Total Hours
Client: - 2 -30
ETL: - 2 - 30
ASCX: - 1 - 20
Bridge: - 1 - 10

Getting the first instance of a keyword is easy - just substring; but finding the nested keyword is proving difficult.

Can nested searches开发者_JS百科 be done within SQL?

any takers?

UPDATE (originally posted as an "answer"):

Further examples:

Let's say I have two records with the following project titles:

Record 1: Interface: ETL:
Record 2: ETL:

Record 1 has 10 hours and record 2 has 30 hours.

Right now, my code captures the first keyword instance, so my output right now is (keyword: hours)

ETL: 30
Interface: 10

However, the requirement is to show that ETL has 40 hours allocated, since two projects had ETL as a keyword:

ETL: 40
Interface: 10

So sure, I can use a LIKE to find all instances of ETL, or Interface, but I need to break down in the select each keyword. For in the above example, if I used a like '%ETL:%' I would get both records, but I want to see all hours for all keywords, broken down by keyword.

Maybe a better question would be:

How can I get a record that looks like this:

Interface: ETL:

Into an output that looks like this:

Interface:
ETL:

within sql?

Not very pretty, but you can use the MODEL clause to do the split (just one way). Assuming you have separate project and project_hours tables with relationship through an ID field:

create table projects as (
    select 'Client: ETL: ASCX: update the import process' as project_title,
        1 as project_id from dual
    union all select 'Client: ETL: Bridge: something else', 2 from dual
    --union all select 'Interface: ETL:', 3 from dual
    --union all select 'ETL:', 4 from dual
)
/

create table project_hours as (
    select 1 as project_id, 20 as hours from dual
    union all select 2, 10 from dual
    --union all select 3, 10 from dual
    --union all select 4, 30 from dual
)
/

This:

with tmp_tab as (
    select project_id, trim(t) as keyword, i
        from projects
        model
        return updated rows
        partition by (project_id)
        dimension by (0 i)
        measures (project_title t)
        rules (t[for i from 1 to
                (length(regexp_replace(':' || t[0],'[^:]')) - 1) increment 1]
            = regexp_substr(t[0],'[^:]+',1,cv(i)))
    order by project_id, i
)
select tt.keyword,
    count(distinct tt.project_id) as total_projects,
    sum(h.hours) as total_hours
from tmp_tab tt
left join project_hours h on h.project_id = tt.project_id
group by tt.keyword
/

Gives this:

KEYWORD                          TOTAL_PROJECTS     TOTAL_HOURS        
-------------------------------- ------------------ ------------------ 
Bridge                           1                  10                 
ETL                              2                  30                 
Client                           2                  30                 
ASCX                             1                  20                 

Edit Or if your second set of examples is included, gives:

KEYWORD                          TOTAL_PROJECTS     TOTAL_HOURS        
-------------------------------- ------------------ ------------------ 
ETL                              4                  70                 
Bridge                           1                  10                 
Interface                        1                  10                 
Client                           2                  30                 
ASCX                             1                  20                 

Adapted from an answer here - so any credit should go to Rob van Wijk really.

I've assumed the keywords are always followed by a colon and anything after the last colon should not be treated as a keyword, which just needed the addition of a -1 to the length().

select count(*) as projectcount, sum(hours) as totalhours from projects WHERE title like '%mykeyword:%';