CODEWITHSUNDEEP
sqlsql-serversql-update
Earth
Earth

Reputation: 3571

Update field value based on file extension

Table having 2 fields

FileName and FileType

 FileName         FileType

File1.txt           png

File2.png           docx

File3.docx          txt

I want to update the FileType fields based on the corresponding file extensions in FileName field

Upvotes: 0

Views: 91

Answers (1)

rory.ap
rory.ap

Reputation: 35318

This should work, even in cases where there is no file extension in FileName:

UPDATE MyTable
SET FileType = NULLIF(SUBSTRING(FileName, LEN(FileName) - 
CHARINDEX('.', REVERSE(FileName)) + 2, LEN(FileName)), '')

Basically it sets the FileType value to a substring pulled from the FileName field starting right after the last index of the . character. If there is no file extension in FileName, e.g. File4, it will set FileType to NULL.

Upvotes: 1

Related Questions