CODEWITHSUNDEEP
sqlsql-server
Adam
Adam

Reputation: 61

Possible to Search Partial Matched Strings from same table?

I have a table and lets say the table has items with the item numbers:

12345
12345_DDM
345653
2345664
45567
45567_DDM

I am having trouble creating a query that will get all of the _DDM and the corresponding item that has the same prefix digits.

So in this case I'd want both 12345 and 12345_DDM etc to be returned

Upvotes: 1

Views: 45

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37337

Try this query:

--sample data
;with tbl as (
    select col from (values ('12345'),('12345_DDM'),('345653'),('2345664'), ('45567'),('45567_DDM')) A(col)
)

--select query
select col from (
    select col, 
           prefix, 
           max(case when charindex('_DDM', col) > 0 then 1 else 0 end) over (partition by prefix) [prefixGroupWith_DDM]
    from (
        select col,
               case when charindex('_DDM', col) - 1 > 0 then substring(col, 1, charindex('_DDM', col) - 1) else col end [prefix]
        from tbl
    ) a
) a where [prefixGroupWith_DDM] = 1

Upvotes: 0

jarlh
jarlh

Reputation: 44696

Use like to find rows with _DDM.

Use EXISTS to find rows with numbers also having a _DDM row.

working demo

select *
from tablename t1
where columnname LIKE '%_DDM'
   or exists (select 1 from tablename t2
              where t1.columnname + '_DDM' = t2.columnname)

Upvotes: 6

Related Questions