Solved

Write a function

Posted on 2016-11-07
5
34 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
  • 3
5 Comments
 
LVL 28

Expert Comment

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

Output --- aa ?
1
 
LVL 10

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 28

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 28

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

776 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