Solved

Trim a File name

Posted on 2014-04-07
4
296 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now