Selecting the last 3 characters of a string SQL

Question

can someone help me with some SQL I am struggling with.

I need to select the final 3 characters of a field containing a file name (so I can grab the extension of said files). Ideally, I'd like a count of these values.

Selecting the first 3 characters of a string is simple enough, but I don't know how to select the final 3 - the filenames are all differing lengths.

Thank you.

No I think he means, add tags like how you added "sql", "string" and "count" — devnation, Aug 22 '17 at 14:15
An example of the contents of the field would be :- Images/Batch_02/000131343_01.pdf Images/Batch_02/000131365_01.pdf kestrel\batch30\000177170_01.zip — b0redj0rd, Aug 22 '17 at 14:15

score 6 · Accepted Answer · answered Aug 22 '17 at 14:14

Two common ways are:

select right(filename, 3)
select substr(filename, -3)

However, I would caution you that not all filenames have 3 characters. I would suggest that you ask another question, with more information and examples of the filenames you are using.

score 0 · Answer 2 · answered Aug 22 '17 at 14:17

In case the file extension isn't only 3 characters... and you don't have any periods anywhere else.

declare @somefile varchar(64) = '/server/folder/sub_folder/file.extension'

select
    substring(@somefile,charindex('.',@somefile) + 1,99)

Or if you do have periods in the file path which is possible but not wise.

declare @somefile varchar(64) = '/ser.ver/fo.lder/sub.folder/file.extension'

select
    right(@someFile,charindex('.',reverse(@somefile)) - 1)

Selecting the last 3 characters of a string SQL

2 Answers2