how to get file name from database.

You are storing the information incorrectly IMO, the concatenated filename is really three discrete peices of information, it would be better to form what you have in the select statement from these. I suggest:

1. Create a new table FooAllowedFileNameExtension
2. In the table add a column Extension, make it varchar type and restict its length as per your requirements probably 1+
3. Make the column in 2 its primary key

With your current table split the filename you have into three columns:
OrininalFilename, Datestamp and extension (which FKs back to the table above).

This seems like a lot of effort, but:
a) You don't lose the original name.
b) You can query better: All files uploaded on a day etc
c) You can used the allowed extensions table to determine whether a file can be added.
d) If you want to add new file types it becomes much easier: just add it to the new table.
e) The acceptable filnames are lower down the stack, so if a new system connects to it, it is less likely that the developer will allow the uploading of an unsupported file type.

Obviously you'll need to migrate the existing data in the first place, and it doesn't really answer your original problem (which both need the ability to split the string) and you may not be able to alter the schema.
You can get the ABD part relatively and file extensions pretty easily:

declare @foo varchar(max)
set @foo = 'ABD_YYYY-MM-DD_TIME.txt'
Select substring( @foo, 0, CHARINDEX ('_',@foo, 0))
Select substring( @foo, CHARINDEX ('.',@foo, 0)+ 1,LEN(@foo) -CHARINDEX ('.',@foo, 0) )

This looks like a useful discussion[^] it discusses various custom split functions.