How to get Duplicate substrings in DB2 SQL

Transact-SQL
1

Hi Team,
How to get duplicate sub string in DB2 sql my scenario is that, I have one column

col1

| ABC_1 |

| ABC_3 |

| WXY_5 |

I want to get only Duplicate sub string data with there addition of remaining sub values

COLUMNS->
String addition of sub string values

| ABC | | 4 |

| WXY | | 5 |

4 is the addition of duplicate sub string value for that I have used query is that SUM(CAST(SUBSTR(a.ref1, LOCATE('-',a.ref1)+1) as DECIMAL)) as output

but how to do it ?
Please give me a any suggestion ?

2

People on this forum is dedicated to Microsoft SQL Server, so you might get better help on a DB2 forum.

Try this:

select case
          when col1 like '%\_%' escape '\'
          then left(col1,posstr(col1,'_')-1)
          else col1
       end as col1
      ,sum(case
              when col1 like '%\_%' escape '\'
              then cast(right(col1,length(col1)-posstr(col1,'_')) as int)
              else 0
           end
          )
       as addition
  from yourtable
 group by case
             when col1 like '%\_%' escape '\'
             then left(col1,posstr(col1,'_')-1)
             else col1
          end
;
3

okay, thank you for response!

4

try this:

SELECT T2.VAL, sum(T3.VAL) total
FROM RIDW.VW_REQUEST T1
INNER JOIN RICALM.VW_RQST_STRING_EXT T2 ON T2.REQUEST_ID=T1.REQUEST_ID AND T2.NAME='owner_1' AND T1.REQUEST_ID <> -1
INNER JOIN JOIN RICALM.VW_RQST_DECIMAL_EXT T3 ON T3.REQUEST_ID=T1.REQUEST_ID AND T3.NAME='resource1'
WHERE (T1.PROJECT_ID, T1.REQUEST_TYPE, T1.ISSOFTDELETED) = (0, 'Task', 0)
group by T2.VAL

SQL Server Support !!