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

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
mphillip85Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ZberteocConnect With a Mentor Commented:
Use this:


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

Open in new window

0
 
Patrick MatthewsCommented:
More details, please.  Such as what is stored now, and how you want it split up.  Use examples,
please.
0
 
mphillip85Author Commented:
Select FileNameImport FROM Table

expr1 = Mid(myFileNameImport,5)
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Patrick MatthewsCommented:
mphillip85,

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

Regards,

Patrick
0
 
mphillip85Author Commented:
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
 
Patrick MatthewsCommented:
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
 
Rick_RickardsCommented:
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
 
mphillip85Author Commented:
"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
 
ZberteocCommented:
Replace _ with the character you want:
UPDATE tbl SET 
	othercolumn=SUBSTRING(FileNameImport ,charindex('_',FileNameImport),len(FileNameImport))
FROM 
	YourTable tbl

Open in new window

0
 
ZberteocCommented:
You might need

charindex('_',FileNameImport)+1

if you want to get the name starting with the caharacter after the one you search.
0
 
Patrick MatthewsCommented:
mphillip85 said:
>>"Everything following the first space"

If what you want is the first space, then why are you using an underscore?
0
 
mphillip85Author Commented:
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
 
mphillip85Author Commented:
Actually a specific character is what I meant
0
 
ZberteocCommented:
Just replace the _ with "\".
0
 
ZberteocCommented:
Replace the _ with whatever character you need. I speciffied that already.
0
 
mphillip85Author Commented:
how do i get the rest of the name.  I am getting the index now
0
 
ZberteocCommented:
What index are you talking about ???
0
 
ZberteocCommented:
Give me an example of file name here and what you want to extract from it.
0
 
mphillip85Author Commented:
12122007142501_Company_test1_11012007.rtf
and i want
Company_test1_11012007.rtf


thanks
0
All Courses

From novice to tech pro — start learning today.