?
Solved

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

Posted on 2011-10-11
2
Medium Priority
?
272 Views
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|.  

Example

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.  
0
Comment
Question by:naisnet
2 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 2000 total points
ID: 36949938
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.

Greg


CREATE FUNCTION dbo.fnStringCompare(@String1 VARCHAR(MAX), @String2 VARCHAR(MAX))  
RETURNS INTEGER
AS  

BEGIN  

DECLARE @Result INTEGER
  
 ;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 
  
END

Open in new window

0
 
LVL 2

Author Closing Comment

by:naisnet
ID: 36950683
Excellent
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 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