6 Replies Latest reply: Nov 27, 2009 12:04 AM by benton RSS

    Getting the last 2,3 or 4 characters in a string

    benton
      Hi,

      If I had a number of files with file names that where different lengths and they had various length file extensions, how could I just display the last 2,3 or 4 characters.

      Example
      fileName_of_this_lengh.ps
      fileName_with_a_longer_length.doc
      fileName_this_length.xls
      fileName_with_a_really_long_length_like_this.tiff

      Is it possible to extract this:
      .ps
      .doc
      .xls
      .tiff

      Thank You
      Ben


      I have had a bit of a play around and come up with the following:
      SELECT CASE WHEN substr (file_name, -3) = '.ps' THEN '.ps'
                  WHEN substr (file_name, -4) = '.zip' THEN '.zip' 
                  WHEN substr (file_name, -4) = '.doc' OR substr (file_name, -4) = '.DOC' THEN '.doc' 
              END format
        FROM tableName
      But is there a smarter way to do this?

      Edited by: Benton on Nov 27, 2009 9:34 AM