1

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######

Community
  • 1
  • 1
Josh McGee
  • 75
  • 8
  • i've added the sql-server tag as the syntax looks like it. – Vamsi Prabhala Jan 31 '17 at 22:36
  • 2
    I'm pretty sure that has nothing to do with trimming/extensions and everything to do with `char(10)`. – melpomene Jan 31 '17 at 22:37
  • 1
    https://www.postgresql.org/docs/current/static/datatype-character.html: "*If the string to be stored is shorter than the declared length, values of type `character` will be space-padded*" (I'd link the MSDN SQL Server docs, but they don't actually mention this) – melpomene Jan 31 '17 at 22:44
  • Thank you @melpomene for your quick response. That was my problem. – Josh McGee Jan 31 '17 at 22:54

1 Answers1

0

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.

andrews
  • 2,173
  • 2
  • 16
  • 29
  • Thank you for the quick response! That was the problem. – Josh McGee Jan 31 '17 at 22:46
  • you're welcome, but @melponene was the first by time in comments...though it's really obvious when looking at table definition. Don't use CHAR datatype unless you know exactly that you will need every character from it. If unsure whether all characters will be populated for a given column value, use varchar(length). See if you need to update aFile and aFilename columns too. I think those should be varchar() either. – andrews Jan 31 '17 at 22:50
  • @JoshMcGee Josh, I understand it was an easy fix but would appreciate if you accept the answer. Also, I've voted up your question so now you have >15 rep and can vote up questions and answers yourself ;). – andrews Feb 01 '17 at 15:57