Solved

How to take part of a text column and create a new column based on it

Posted on 2007-12-06
19
172 Views
Last Modified: 2010-03-20
I have a column named filenameimport.
So i want to take a piece of that name and put it into a new column
What can I do

Thanks
0
Comment
Question by:mphillip85
[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
  • 7
  • 7
  • 4
  • +1
19 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 20422367
More details, please.  Such as what is stored now, and how you want it split up.  Use examples,
please.
0
 

Author Comment

by:mphillip85
ID: 20422457
Select FileNameImport FROM Table

expr1 = Mid(myFileNameImport,5)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 20422519
mphillip85,

Your response is not very helpful.  Are you saying you always want to break out the 5th through Nth
characters?

Regards,

Patrick
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mphillip85
ID: 20422597
I have a column in a view called FileNameImport
I want to put another column that has just a specific part of the filenameimport.

Suchas  Record1, so I want to make another expr1 = cord1

is this more helpful
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 20422622
mphillip85 said:
>>is this more helpful

Frankly, no, it is not.

I need you to state a rule for dividing the string.  Without that rule, I cannot help you.

The rule could be something like:

"Everything from the 5th character and over"
"Everything following the first space"
"Everything following the last space"
"Everything between 'x' and 'y'"

or something more complex.  But there has to be a rule.
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20422688
hate to burst anyones bubble but matthewspatrick is right.  Without more information all we can really do is GUESS as to what you want.

What would be helpful is to say Something like this...

I have...

C:\folder1\folder2\file.txt

I want...

file.txt

*************

I have

C:\My Documents\My Name\Sample.pdf

I want...

Sample.pdf

************

Basically we need to have some idea what you are starting with and what you want to end up with when the query is done.  As soon as you can offer insight on that I have no doubt you're going to get a good answer.

Rick
0
 

Author Comment

by:mphillip85
ID: 20422733
"Everything following the first space"

I can give you an example of what I would do in VB .NET

myFilename = Mid(FileNameImport,InStr(FileNameImport,"_"))

So this looks for the character where it is located and gives it back to the MID which then gives me the
part of filenameimport (String) that I want.

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20422986
Replace _ with the character you want:
UPDATE tbl SET 
	othercolumn=SUBSTRING(FileNameImport ,charindex('_',FileNameImport),len(FileNameImport))
FROM 
	YourTable tbl

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20422999
You might need

charindex('_',FileNameImport)+1

if you want to get the name starting with the caharacter after the one you search.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 20423083
mphillip85 said:
>>"Everything following the first space"

If what you want is the first space, then why are you using an underscore?
0
 

Author Comment

by:mphillip85
ID: 20423090
now you showed me the character find for the index.
Now I need to get the last part of the filename, like
C:\test1
I need
test1
0
 

Author Comment

by:mphillip85
ID: 20423113
Actually a specific character is what I meant
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20423120
Just replace the _ with "\".
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20423133
Replace the _ with whatever character you need. I speciffied that already.
0
 

Author Comment

by:mphillip85
ID: 20423135
how do i get the rest of the name.  I am getting the index now
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20423139
What index are you talking about ???
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20423145
Give me an example of file name here and what you want to extract from it.
0
 

Author Comment

by:mphillip85
ID: 20423171
12122007142501_Company_test1_11012007.rtf
and i want
Company_test1_11012007.rtf


thanks
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 20423309
Use this:


UPDATE tbl SET 
	othercolumn=SUBSTRING(FileNameImport ,charindex('_',FileNameImport)+1,len(FileNameImport))
FROM 
	YourTable tbl

Open in new window

0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MYSQL responding very slow 3 49
IIF in access query 19 40
Current Month Filter in Visual Studio 10 40
How to trim a value in SQL 2 19
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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