T-SQL - Example for SUBSTRING with CASE statement

This post is just to show an example for SUBSTRING with CASE statement

Sample Code:

DECLARE @tmp TABLE (FullFileName VARCHAR(100))
INSERT @tmp SELECT 'File_2013-04-21.txt'
INSERT @tmp SELECT 'File_2013-04-21.txt -1'
INSERT @tmp SELECT 'File_2013-04-21.txt -2'

SELECT FullFileName,
SUBSTRING(FullFileName,1,
CASE WHEN (CHARINDEX(' -',FullFileName)) > 0
THEN CHARINDEX(' -',FullFileName)
ELSE LEN(FullFileName) END ) [FileName]
,CASE WHEN CHARINDEX(' -',SUBSTRING(FullFileName,CHARINDEX(' -',FullFileName,0),LEN(FullFileName))) > 0
THEN SUBSTRING(FullFileName,CHARINDEX(' -',FullFileName,0),LEN(FullFileName))
ELSE NULL END Filesuffix
FROM @tmp

See Also:

• My other posts on T-SQL