CODEWITHSUNDEEP
sqlstringcountsql-server-2016
b0redj0rd
b0redj0rd

Reputation: 53

Selecting the last 3 characters of a string SQL

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.

Upvotes: 2

Views: 21854

Answers (2)

S3S
S3S

Reputation: 25142

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)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270733

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.

Upvotes: 8

Related Questions