Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | SQL Puzzle | Get Last 4 Characters from a String without Left & Right Function

This question was asked to me in one of the technical interviews I had recently attended. The puzzle is like you have to get last 10 characters from a string and you cannot use LEFT and RIGHT inbuilt functions.

Please check out the sample input values and sample expected output below.

Sample Input

Sample input : 123____ [123 & the 4 spaces are there]

Expected Output

Expected Output will be : ____ [4 spaces]

Rules/Restrictions

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

SOLUTION 1 | Using DataLength & Substring


--

DECLARE @ AS VARCHAR(1000) = '123    '

SELECT RIGHT(@,4) Last4Characters

SELECT REVERSE(LEFT(REVERSE(@),4)) Last4Characters

SELECT SUBSTRING(@,DATALENGTH(@) - 4 + 1 ,DATALENGTH(@)) Last4Characters

--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements