Solved

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

Posted on 2011-10-11
Medium Priority
272 Views
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
Question by:naisnet

LVL 21

Accepted Solution

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
``````
0

LVL 2

Author Closing Comment

ID: 36950683
Excellent
0

## Featured Post

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…
###### Suggested Courses
Course of the Month16 days, 7 hours left to enroll