February 23, 2009 at 11:47 am
I have data coming in a column in the following format
02968-01999-00013 - 9.The length of the data is not fixed,but 1 thing for sure it is the last 4 char of the data consist of ' - 9'.I need to come up with the best way to get rid of the last 4 char of the data coming in as part of an existing SSIS project?Which transformation can I use for this?
Which mean a way to store and loop through all rows within the column and remove the last 4 char ?
I know that we can use SUBSTRING together with CHARINDEX Or SUBSTRING with LENGTH , but these functions require to pass the data instead of the column name.
Do advise.Thank You
February 23, 2009 at 11:57 am
You can use the derived column transform and use the Left function.
Left(column, Len(column)-4)
Should work.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 23, 2009 at 12:24 pm
Jack,
Thanks for your help.
I have used the following
Update [TEST]
set Id=substring(Id,1,charindex(' ',Id)-1)
where Id <>''
** There were empty data being passed to the column,therefore the above was erroring out.I fixed it with Where clause.AND yes you can pass the column name as well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply