CODEWITHSUNDEEP
sql-server
Akhil Rajan
Akhil Rajan

Reputation: 43

Compare Two Comma Separated String return Matching String Index,matching count,blank count

How to compare to String of type below 

@Qstring='C,D,B,C,D,C,E,B,E,A,D,'
@Astring='C,D,C,E,D, ,E, ,E, ,A,'

and produce the string similar below. is there method compare without using loop and temp table

Blankstring='6,8,10' 
BlankCount=3
MatchingString=1,2,5,7,9
matchcount =5

Upvotes: 1

Views: 104

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175796

You probably shouldn't do it in DB but why not:

Warning: this is sketch only and should be improved if needed.

SqlFiddleDemo

Code:

DECLARE 
   @Qstring NVARCHAR(100) ='C,D,B,C,D,C,E,B,E,A,D,',
   @Astring NVARCHAR(100) ='C,D,C,E,D, ,E, ,E, ,A,';

DECLARE  
   @Qxml XML = CONVERT(XML,'<Vals><Val>' + REPLACE(LEFT(@Qstring,LEN(@Qstring)-1),',', '</Val><Val>') + '</Val></Vals>'),
   @Axml XML = CONVERT(XML,'<Vals><Val>' + REPLACE(LEFT(@Astring,LEN(@Astring)-1),',', '</Val><Val>') + '</Val></Vals>'); 

;WITH QStringTab AS
(
   SELECT
       [val] = x.i.value('.', 'NVARCHAR(10)')
       ,[rn] = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
   FROM @Qxml.nodes('/Vals/Val') AS x(i)
), AStringTab AS
(
   SELECT
       [val] = x.i.value('.', 'NVARCHAR(10)')
       ,[rn] = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
   FROM @Axml.nodes('/Vals/Val') AS x(i)
), matchcount AS
(
   SELECT c = COUNT(*) 
   FROM QStringTab q
   JOIN AStringTab a
     ON q.rn = a.rn
    AND q.val = a.val
), blankcount AS
(
   SELECT c = COUNT(*)
   FROM AStringTab a
   WHERE val = ''
), blankstring AS
(
  SELECT 
   [s] =  STUFF( (SELECT ',' + CAST(a.rn AS NVARCHAR(10))
                 FROM AStringTab a
                 WHERE a.val = ''
                 ORDER BY rn ASC
                 FOR XML PATH('')), 
                 1, 1, '')
), matchingstring AS
(
  SELECT 
   [s] =  STUFF( (SELECT ',' + CAST(a.rn AS NVARCHAR(10))
                 FROM QStringTab q
                 JOIN AStringTab a
                   ON q.rn = a.rn
                  AND q.val = a.val
                 ORDER BY q.rn ASC
                 FOR XML PATH('')), 
                 1, 1, '')
)
SELECT [statistics] = 'BlankString = ' + ISNULL(bs.s, '')
FROM blankstring bs
UNION ALL
SELECT [statistics] = 'BlankCount = ' + CAST(b.c AS NVARCHAR(100))
FROM blankcount b
UNION ALL
SELECT [statistics] = 'MatchingString = ' + ISNULL(ms.s, '')
FROM matchingstring ms
UNION ALL
SELECT [statistics] = 'Matchcount = ' + CAST(m.c AS NVARCHAR(100))
FROM matchcount m;

Doubts:

  • I assume that you want to compare values between commas and strings have the same number of commas
  • BlankCount should count from both or only @AString?
  • BlankString only for @AString, what if there are blank in both strings?
  • MatchingString should match blanks?
  • I removed last comma because it will be one more blank

Upvotes: 2

Related Questions