• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 50
  • Last Modified:

Write a function

How can I write a function f(a, b) which takes two character string arguments and returns a string containing only the characters found in both strings.
0
BehrangDBA
Asked:
BehrangDBA
  • 3
1 Solution
 
Pawan KumarDatabase ExpertCommented:
string1 ------ Pawan
string2 ------ Ra

Output --- aa ?
1
 
HuaMinChenBusiness AnalystCommented:
Hi,
You need to use loop to scan every character of the string, to compare with the other string.
0
 
Pawan KumarDatabase ExpertCommented:
Try this..

CREATE FUNCTION MatchString
(
	 @String1 VARCHAR(1000)
	,@String2 VARCHAR(1000)
)
RETURNS @Table TABLE 
(
   Matchs VARCHAR(1000)
)
AS
BEGIN

INSERT INTO @Table
SELECT m.* FROM 
(
	SELECT SUBSTRING(@String1,number,1) Chr
	FROM
	( 
			 SELECT DISTINCT number FROM MASTER..SPT_VALUES AS num1 
			 WHERE num1.Number >= 1
		AND  num1.Number <= DATALENGTH(@String1)
    
	)u
)m	
INNER JOIN 
(
	SELECT SUBSTRING(@String2,number,1) Chr
	FROM
	( 
			 SELECT DISTINCT number FROM MASTER..SPT_VALUES AS num1 
			 WHERE num1.Number >= 1
		AND  num1.Number <= DATALENGTH(@String2)
    
	)u	
)k ON m.Chr = k.Chr

RETURN

END

Open in new window



Usage


SELECT * FROM MatchString('Pawan','a')

Open in new window



Output

Matchs
--------------
a
a


If you dont want to Use Master..Spt_Values (System Table) use below

--

CREATE FUNCTION MatchStringSS
(
	 @String1 VARCHAR(1000)
	,@String2 VARCHAR(1000)
)
RETURNS @Table TABLE 
(
   Matchs VARCHAR(1000)
)
AS
BEGIN

WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3   
)
INSERT INTO @Table
SELECT m.* FROM 
(
	SELECT SUBSTRING(@String1,number,1) Chr
	FROM
	( 
			 SELECT DISTINCT number FROM Series AS num1 
			 WHERE num1.Number >= 1
		AND  num1.Number <= DATALENGTH(@String1)
    
	)u
)m	
INNER JOIN 
(
	SELECT SUBSTRING(@String2,number,1) Chr
	FROM
	( 
			 SELECT DISTINCT number FROM Series AS num1 
			 WHERE num1.Number >= 1
		AND  num1.Number <= DATALENGTH(@String2)
    
	)u	
)k ON m.Chr = k.Chr

RETURN

END

--

Open in new window



Usage


SELECT * FROM MatchString('Pawan','a')

Open in new window



Output

Matchs
--------------
a
a


Hope it helps !!
1
 
BehrangDBAAuthor Commented:
Thank you very much Pawan
0
 
Pawan KumarDatabase ExpertCommented:
Welcome BehrangDBA !!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now