Solved

Write a function

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

732 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