06.13.08

Extract file name extension from file path using T-SQL

Posted in Programming at by chenty

HTTPRequestURIStem is the column contains file path, e.g. /webpage/default.aspx

SQL [Show Plain Code]:
  1. REVERSE(SUBSTRING(REVERSE(REVERSE(SUBSTRING(REVERSE(HTTPRequestURIStem),0,
  2. CHARINDEX(‘/’,REVERSE(HTTPRequestURIStem))))),0,
  3. CHARINDEX(‘.’,REVERSE(REVERSE(SUBSTRING(REVERSE(HTTPRequestURIStem),0,
  4. CHARINDEX(‘/’,REVERSE(HTTPRequestURIStem))))))))

This function will return “aspx” for “/webpage/default.aspx”.

NOTICE: I put the code in seperate lines for readablity.

Leave a Comment