Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trim a File name

Posted on 2014-04-07
4
Medium Priority
?
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 22

Assisted Solution

by:plusone3055
plusone3055 earned 400 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 23

Accepted Solution

by:
Steve Wales earned 1600 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

704 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