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

*/

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.

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