Solved

Trim a File name

Posted on 2014-04-07
4
302 Views
Last Modified: 2014-04-07
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
0
Comment
Question by:tesla764
4 Comments
 
LVL 22

Assisted Solution

by:plusone3055
plusone3055 earned 100 total points
ID: 39984356
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
 
LVL 22

Accepted Solution

by:
Steve Wales earned 400 total points
ID: 39984371
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
 

Author Closing Comment

by:tesla764
ID: 39984402
Thanks.
0
 
LVL 1

Expert Comment

by:JoseBarroso
ID: 39984408
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

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question