Tags

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


SQL Puzzle | The File Extension puzzle

In this puzzle you have to find out the extension of the file names present in a table. Note the file name may contain a .(dot) in its name. For more details please check the sample input and expected output.

Note – In T-SQL we do not have any function which will give us the last occurrence of any letter/string.

Sample Input

Id fname
1 f1.xlsx
2 file2.doc
3 fl.h
4 testfile.abcxyz
5 t…est..file.abcxyz

Expected Output

fName Extension
f1.xlsx .xlsx
file2.doc .doc
fl.h .h
testfile.abcxyz .abcxyz
t…est..file.abcxyz .abcxyz

Script

Use below script to create table and insert sample data into it.

--


SELECT * INTO FileNames1 FROM 
	(
		SELECT 1 Id ,'f1.xlsx' fname UNION ALL 
		SELECT 2,'file2.doc' fname  UNION ALL
		SELECT 3,'fl.h' fname   UNION ALL
		SELECT 4,'testfile.abcxyz' fname  UNION ALL
		SELECT 5,'t...est..file.abcxyz' fname  
	)z



--

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

--

 SELECT fName , '.'+REVERSE(SUBSTRING(REVERSE(FNAME),0,CHARINDEX('.', REVERSE(FNAME),0 ))) Extension FROM FileNames1


--

Output-1

--

 
/*------------------------
SELECT fName , '.'+REVERSE(SUBSTRING(REVERSE(FNAME),0,CHARINDEX('.', REVERSE(FNAME),0 ))) Extension FROM FileNames1
------------------------*/
fName                Extension
-------------------- ---------------------
f1.xlsx              .xlsx
file2.doc            .doc
fl.h                 .h
testfile.abcxyz      .abcxyz
t...est..file.abcxyz .abcxyz

(5 row(s) affected)




--

Solution – 2

--

SELECT fName ,RIGHT(FNAME , CHARINDEX('.' ,REVERSE(FNAME))) Extension FROM FileNames1

--

Output-2

--

/*------------------------
SELECT fName ,RIGHT(FNAME , CHARINDEX('.' ,REVERSE(FNAME))) Extension FROM FileNames1
------------------------*/
fName                Extension
-------------------- --------------------
f1.xlsx              .xlsx
file2.doc            .doc
fl.h                 .h
testfile.abcxyz      .abcxyz
t...est..file.abcxyz .abcxyz

(5 row(s) affected)



--

Other Reference Puzzles for Last Puzzles

1 https://msbiskills.com/2016/05/13/sql-puzzle-get-last-4-characters-from-a-string-without-left-right-function/

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