Try this:

SET NOCOUNT ON

DECLARE @Command VARCHAR(100)

SET @Command = 'dir /b /s '

DECLARE @Folder VARCHAR(100)

SET @Folder = 'D:\AS'

DECLARE @FilesInAFolder TABLE (FileNamesWithFolder VARCHAR(500))

INSERT INTO @FilesInAFolder

EXEC MASTER..xp_cmdshell @Command

; WITH CTE AS

(

SELECT REVERSE(FileNamesWithFolder) ReverseFileNames FROM @FilesInAFolder

)

SELECT --FileNames = STUFF ( FileNamesWithFolder , 1 , (LEN(FileNamesWithFolder) - CHARINDEX ('\', REVERSE(FileNamesWithFolder))+1) , '')

FileNames = REVERSE ( LEFT (ReverseFileNames, CHARINDEX ('\', ReverseFileNames)-1))

FROM CTE

WHERE ReverseFileNames IS NOT NULL