Solved

Trim a File name

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

752 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