Search code examples
sqlsql-servercharacter-trimming

Why is SQL code to get the extension adding extra spaces?

I followed the example in this answer Get extension of a file using SQL? to extract the extension from a list of files in a folder. It's working great, but is throwing in 6 extra spaces. I've tried adding an rtrim, but that hasn't helped, unless I'm putting it in the wrong position.

create table images
(
id int IDENTITY(1,1) PRIMARY KEY,
PartNo char(10),
aFileName char(255),
extension char(10),
aFile char(255),
depth int,
isFile bit
)

insert images (aFile, depth, isFile)
EXEC xp_dirtree 'C:\Program Files\e-Con Solutions\e-Con 2012 R2\Web\images\coverpools', 10, 1

This returns filenames such as 010001.eprt

update images   
SET extension = rtrim(REVERSE(
                       left(rtrim(REVERSE(aFile)), 
                       case when CHARINDEX('.', REVERSE(aFile) ) = 0 then LEN(REVERSE(aFile)) 
                       else CHARINDEX('.', REVERSE(aFile))-1 end)
                       ))

This is the code that is returning Extension eprt######

Solution

  • you need to change your table definition and convert the extension column from extension char(10) to extension varchar(10) as @melpomene have pointed out in comments.