Solved

Write a function

Posted on 2016-11-07
5
41 Views
Last Modified: 2016-11-08
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
Comment
Question by:BehrangDBA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 29

Expert Comment

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

Output --- aa ?
1
 
LVL 11

Expert Comment

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

Accepted Solution

by:
Pawan Kumar earned 500 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

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
 

Author Closing Comment

by:BehrangDBA
ID: 41878759
Thank you very much Pawan
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41879908
Welcome BehrangDBA !!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

696 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