How to calculate the number of some entries duplicate contain the word in the column in sql server ?

sraj muneer 21 Reputation points
2020-12-28T16:52:30.58+00:00

example : I'm search for some text in columns use (where [all] like 'samei' and 'sudi' and 'majk') only

my table
-----------------column-ID---- column-allnames

                1        samei sudi

                2        sudi majk

                3        jax fadi

                4        sudi samei

                5        fadi  arm

I want result
--------------------column-ID--- column-allnames ---- column-count

               1        samei                 2

               2         sudi                 3

               3         majk                 1
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,651 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 118K Reputation points
    2020-12-28T17:04:57.647+00:00

    Try something like this:

    declare @mytable table ( ID int, [all] varchar(max) )  
      
    insert @mytable values  
    ( 1, 'samei sudi' ),  
    ( 2, 'sudi majk' ),  
    ( 3, 'jax fadi' ),  
    ( 4, 'sudi samei' ),  
    ( 5, 'fadi  arm' )  
      
    select   
        id,  
        w as [all],  
        (select count(*) from @mytable where  
                [all] like w + ' %' or   
                [all] like '% ' + w + ' %' or   
                [all] like '% ' + w or   
                [all] = w ) as [count]  
    from (values (1, 'samei'), (2, 'sudi'), (3, 'majk')) t(id, w)  
    

    or

    select   
        id,  
        w as [all],  
        (select count(*) from @mytable cross apply string_split([all], ' ') where [value] = w ) as [count]  
    from (values (1, 'samei'), (2, 'sudi'), (3, 'majk')) t(id, w)  
      
    
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.