Trim a File name

In sql how would I remove the filename characters starting in position 22 for a length of 7    
eg. DXX_ENCOMPASS_OND_BULK_140407.txt
to become
      DXX_EMCOMPASS_OND_BULK.txt
tesla764Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
If it's always going to be the same format:

(assuming you have a variable called @file or something)

select @newfile = substring(@file,1,22)+substring(@file,30,len(@file)-28)

It's hard coded for a specific format (and there's probably a better way to do it, but that should work.

declare @file varchar(50)
declare @newfile varchar(50)
select @file='DXX_ENCOMPASS_OND_BULK_140407.txt'
select @newfile = substring(@file,1,22)+substring(@file,30,len(@file)-28)
print @file
print @newfile

Open in new window


Gives:
DXX_ENCOMPASS_OND_BULK_140407.txt
DXX_ENCOMPASS_OND_BULK.txt
0
 
plusone3055Connect With a Mentor Commented:
you are looking for a TRIM function.
The following link will explain how to do it in 60 seconds :)

Happy Viewing :)

http://blog.sqlauthority.com/2013/01/23/sql-server-trim-function-to-remove-leading-and-trailing-spaces-of-string-sql-in-sixty-seconds-040-video/
0
 
tesla764Author Commented:
Thanks.
0
 
JoseBarrosoCommented:
Hi,

Use the SubString function

Use this example if you know the exact size name of the file:

  SELECT (substring(FileNameField ,1,21)  + '.TXT')  as sFileName  FROM TABLE__ ;

Or use this if that didn't solve with the previews answer:

  SELECT (substring(FileNameField ,1,21) +  
  substring(FileNameField,30,LEN(FileNameField))) as sFileName  FROM TABLE___;

Replace: "FileNameField" by your field name and TABLE___ by your table name.

Hope this help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.