Solved

Write a function

Posted on 2016-11-07
5
25 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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Accepted Solution

by:
Pawan Kumar Khowal 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 18

Expert Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
optimize  c# code 7 49
Sql Stored Procedure 26 28
Database Containment - Benefits 6 26
Auditing with Temporal Tables 4 18
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

760 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now