SQL Puzzle | The Maximum characters matching in a string Puzzle

In the puzzle, you have to start matching the characters in the string from the start and the end.

For example ONION, Here ON from the start matches with ON at the end.
Similarly in case of abab, ab from the start matches with the ab at the end.

Here we have to find out the Order meaning length of the string matches from the start with the end.

For details please check out the sample input and the expected output below-

Sample Inputs & Expected Output

/*

Sample Input strings Expected Output
——————– —————
a Order is : 0
ab Order is : 0
abab Order is : 2
abcdeabcdf Order is : 0
abcdeabcd Order is : 4
onion Order is : 2

*/

The Order Puzzle – MSBISkills

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data

--

DECLARE @ AS VARCHAR(100) = 'onion'

--

Solution 1

--

CREATE FUNCTION fn_Match 
(
	@left VARCHAR(50), @Right VARCHAR(50)
)
RETURNS TINYINT
AS
BEGIN
	DECLARE @returnValue TINYINT=0

	WHILE(1=1)
	BEGIN
			SET @left = LEFT(@left,LEN(@left)-1)
			SET @Right = RIGHT(@Right,LEN(@Right)-1)
		IF @left = @Right
			BEGIN	
				SET @returnValue = LEN(@left)
				BREAK
			END
		ELSE 
			CONTINUE
		END

	RETURN 	@returnValue;
END
GO

DECLARE @ AS VARCHAR(100) = 'onion'
DECLARE @Out AS TINYINT=0

IF LEFT(@,DATALENGTH(@) / 2) = RIGHT(@,DATALENGTH(@) / 2)
	BEGIN
		SELECT CONCAT(' Order is : ', LEN(LEFT(@,DATALENGTH(@) / 2))) [Order]
	END
ELSE
	BEGIN
		SELECT @Out = dbo.fn_Match(LEFT(@,DATALENGTH(@) / 2),RIGHT(@,DATALENGTH(@) / 2)) 	 	
		SELECT CONCAT(' Order is : ' , @Out) [Order]
	END

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com