x
Solved

# Write a function

Posted on 2016-11-07
Medium Priority
53 Views
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
Question by:BehrangDBA
• 3

LVL 38

Expert Comment

ID: 41878105
string1 ------ Pawan
string2 ------ Ra

Output --- aa ?
1

LVL 11

Expert Comment

ID: 41878117
Hi,
You need to use loop to scan every character of the string, to compare with the other string.
0

LVL 38

Accepted Solution

Pawan Kumar earned 2000 total points
ID: 41878143
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

Usage

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

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

--

Usage

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

Output

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

Hope it helps !!
1

Author Closing Comment

ID: 41878759
Thank you very much Pawan
0

LVL 38

Expert Comment

ID: 41879908
Welcome BehrangDBA !!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.