May 14, 2014 at 3:38 am
hi i have the following text in a coulmn
Net Asset Value for Ignis Absolute Return Government Bond Fund . Class A GBP
Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class I2 EUR Hedged
Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class I2 CHF
what im trying to do it when search that column and when i hit the work call i take all the text from clss to end and put it into another column
so when i look at my new column it should just have
Class A GBP
Class I2 EUR Hedged
Class I2 CHF
Anyone any ideas the best way to do this
May 14, 2014 at 4:38 am
ronan.healy (5/14/2014)
hi i have the following text in a coulmnNet Asset Value for Ignis Absolute Return Government Bond Fund . Class A GBP
Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class I2 EUR Hedged
Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class I2 CHF
what im trying to do it when search that column and when i hit the work call i take all the text from clss to end and put it into another column
so when i look at my new column it should just have
Class A GBP
Class I2 EUR Hedged
Class I2 CHF
Anyone any ideas the best way to do this
You want to create a new column and store the suggested data there - is that correct?
How is SSIS involved in this process?
Does your sample data show one row or three?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 14, 2014 at 4:46 am
ronan.healy (5/14/2014)
hi i have the following text in a coulmnNet Asset Value for Ignis Absolute Return Government Bond Fund . Class A GBP
Total Outstanding shares for Ignis Absolute return Government Bond Fund. Class I2 EUR Hedged
Net Asset Value per share for Ignis Absolute return Government Bond Fund. Class I2 CHF
what im trying to do it when search that column and when i hit the work call i take all the text from clss to end and put it into another column
so when i look at my new column it should just have
Class A GBP
Class I2 EUR Hedged
Class I2 CHF
Anyone any ideas the best way to do this
Use FINDSTRING to locate "class" and then use this as input for SUBSTRING.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 14, 2014 at 4:47 am
The data comes in in an excel file the ssis package reads in this file and i want to get it to pick it up.
all the line come in as 1 line at a time
May 14, 2014 at 4:47 am
Or you can use RIGHT instead of SUBSTRING, even easier.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 14, 2014 at 4:50 am
how would the replace work
REPLACE("Mountain Bike", "Mountain","All Terrain")
all my data comes in differently so wont no what it is all thats the same is the class
May 14, 2014 at 4:52 am
ronan.healy (5/14/2014)
how would the replace workREPLACE("Mountain Bike", "Mountain","All Terrain")
all my data comes in differently so wont no what it is all thats the same is the class
OK, I didn't know that 🙂
Then Koen's suggestion is the way to go with FINDSTRING.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 14, 2014 at 4:53 am
Phil Parkin (5/14/2014)
ronan.healy (5/14/2014)
how would the replace workREPLACE("Mountain Bike", "Mountain","All Terrain")
all my data comes in differently so wont no what it is all thats the same is the class
OK, I didn't know that 🙂
Then Koen's suggestion is the way to go with FINDSTRING.
To be safer, you might search for ". Class " - if that is always there.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 14, 2014 at 4:55 am
some of them dont have . class some pain the only thing thats def the same is the word class
May 14, 2014 at 4:58 am
if i use this
RTRIM("Class")
it pulls back class how do i get it to pull it all back without putting more than the word class in it
May 14, 2014 at 5:01 am
ronan.healy (5/14/2014)
if i use thisRTRIM("Class")
it pulls back class how do i get it to pull it all back without putting more than the word class in it
RTRIM removes trailing whitespace, so I'm not sure what you expect.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 14, 2014 at 5:06 am
well i dont no how to pull back what i want
you mentioned use right in one of your messages so i tired it.
im new to ssis so have no clue how to get what i want back.
May 14, 2014 at 5:08 am
misread ur main you had right not rtrim
May 14, 2014 at 5:10 am
ronan.healy (5/14/2014)
well i dont no how to pull back what i wantyou mentioned use right in one of your messages so i tired it.
im new to ssis so have no clue how to get what i want back.
RIGHT, not RTRIM.
RIGHT selects x number of characters from a string starting from the right.
So, if you find the word class starting at position 20 and your string has a total length of 30 (meaning you want the last 10 characters), you use the following formula:
RIGHT(myString, LEN(myString) - FINDSTRING(myString,"class"))
Test the formula first. I believe FINDSTRING starts at zero, so you might want to add or substract 1.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply