CODEWITHSUNDEEP
sqlsql-servert-sql
user3170410
user3170410

Reputation: 61

counting number of instances within a string

I have a column that can have numerous names in it.

I need a way to:

  1. Column 1: Count the total number of names in the field
  2. Column 2: Count the number of names in the field that begins with "mel".

Data examples:

Row 1 || bob hope deloris steve mike ||
Row 2 || melbob melbetty steve       ||

Expected responses would be:

       | Column 1 | Column 2 |
Row 1  |     5    |     0    |
Row 2  |     3    |     2    |

hope this makes sense.

Upvotes: 2

Views: 67

Answers (2)

Martin Smith
Martin Smith

Reputation: 453897

I have a column that can have numerous names in it.

Why? This is violation of first normal form.

If a "name" is any clump of non space characters and they are separated by exactly one space with no leading spaces you can use

Select ID,
       SIGN(L) + L -LEN(REPLACE(YourField,' ','')),
       (1 + L -LEN(REPLACE(' ' + YourField,' Mel','')))/4
 From @YourTable
CROSS APPLY (SELECT Len(YourField)) C(L)

(Borrowing the sample data from the other answer http://rextester.com/KPHAC50383)

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 82010

With the help of a Cross Apply and some XML to split the string

Declare @YourTable Table (ID int,YourField varchar(500))
Insert Into @YourTable values
(1,'bob hope deloris steve mike'),
(2,'melbob melbetty steve')

Select A.ID
      ,AnswerA = Count(*)
      ,AnswerB = Sum(case when RetVal Like 'mel%' then 1 else 0 end)
 From @YourTable A
 Cross Apply (
            Select RetSeq = Row_Number() over (Order By (Select null))
                  ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
            From (Select x = Cast('<x>'+ Replace(A.YourField,' ','</x><x>')+'</x>' as xml).query('.')) as A 
            Cross Apply x.nodes('x') AS B(i)
      ) B
 Group By ID

Returns

ID  AnswerA AnswerB
1   5       0
2   3       2

EDIT - Just to help you visualize this a little better

Select * without the GROUP BY would produce the following. Now, normally I use the code within the CROSS APPLY as a TVF (Table-Valued-Function).

ID  RetSeq  RetVal
1   1       bob
1   2       hope
1   3       deloris
1   4       steve
1   5       mike
2   1       melbob
2   2       melbetty
2   3       steve

Upvotes: 2

Related Questions