Possible Duplicate:
Get substring in SQL Server
Let's say I have MyImage.png
or MyDoc.doc
, etc in a column in a database table.
How can I only get the file extension?
In order to get file extension of file as a result of SQL query, you can use SUBSTRING_INDEX(). Insert some records in the table using insert command. Display all records from the table using select statement.
From w3schools.com/sql/func_sqlserver_charindex.asp: "The position where the search will start (if you do not want to start at the beginning of string). The first position in string is 1." If you have Strings inside your column that have more than 999 chars you have to modify this parameter.
SQL Server supports two types of format files: XML formats and non-XML format files. Both non-XML format files and XML format files contain descriptions of every field in a data file, and XML format files also contain descriptions of the corresponding table columns.
In SQL Server Management Studio, from the Tools menu, click Options. In the Options dialog box, click Text Editor, and then click File Extension. In the Extension box, type your new file extension.
try this:
declare @str varchar(20)='MyDoc.doc'; select reverse(left(reverse(@str),charindex('.',reverse(@str))-1))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With