Return the Percentage of Characters in String A that occur in string B

Posted on 2011-10-11
Last Modified: 2012-05-12
I'm looking to create a function that returns the percentage of characters in string [A] that occur in string |B|.  


String |A| = 'bookmarked'
String |B| = 'bookstands'

Using string |A| as the base, 50% of the characters in string |A| occur in string |B| (b,o,o,k,a).  The percentage should be returned from the function as an integer.  

Thanks, Gurus.  
Question by:naisnet
    LVL 21

    Accepted Solution

    Here is a possible solution.  Something I noticed though.  Your example is not 50%.  If you are looking for the percentage of characters in the first string that are also in the second string the answer is 70%.  (b, o, o, k, a, another k, and d)  Please let me know if this function doesn't do what you were expecting.


    CREATE FUNCTION dbo.fnStringCompare(@String1 VARCHAR(MAX), @String2 VARCHAR(MAX))  
     ;WITH Tally (N) AS   
      SELECT TOP (LEN(@String1) + LEN(@String2)) ROW_NUMBER () OVER (ORDER BY sc1.Object_ID) AS N  
      FROM Master.sys.All_Columns sc1 CROSS JOIN  
       Master.sys.All_Columns sc2  
     String1  AS  
      (SELECT SUBSTRING(@String1,N,1) AS Letter  
      FROM Tally  
      WHERE N <= LEN(@String1)),
     String2 AS
      (SELECT SUBSTRING(@String2,N,1) AS Letter
      FROM Tally  
      WHERE N <= LEN(@String2))
    	SELECT @Result = CONVERT(INTEGER, COUNT(*)/CONVERT(DECIMAL(10, 2), (SELECT COUNT(*) FROM String1))* 100.00)
    	FROM String1
    	WHERE Letter IN(SELECT Letter FROM String2)
     RETURN  @Result 

    Open in new window

    LVL 2

    Author Closing Comment


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    upgrade Vcenter to V6 10 48
    SQL PIVOT Table 21 30
    Why do I get extra rows when I do inner join? 12 29
    query question 4 19
    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now