Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Problem using expression in SSIS Expand / Collapse
Author
Message
Posted Wednesday, August 1, 2012 6:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 6:49 AM
Points: 34, Visits: 250
Hi,

Below is the expression i am trying to make work :

substring ("Archive_Full_20120731200002",1, (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2))


Whe I use the exact string(Archive_Full_20120731200002) , I get the required result i.e Archive
But when I use a variable which contains th same string, the above expression gives the error that
Substring can allow -ve integer values( Here -2)

Please help me in this regard. I need to use a variable here and get it work.

Your help is appreciated!!!

Thanks
Devesh
Post #1338480
Posted Wednesday, August 1, 2012 8:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 5,317, Visits: 12,351
Please post the expression which you tried - the one which gives the error.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1338561
Posted Wednesday, August 1, 2012 10:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 6:49 AM
Points: 34, Visits: 250

Below is the expression to be evaluated:

Substring (@Variable,1, (FINDSTRING( @Variable,"Full_", 1)-2))

@Variable is the variable containing the exact string 'Archive_Full_20120731200003'

But when i used this as a expression, it gives the error that substring does not allow -ve integer values.

Kindly help.
Post #1338681
Posted Wednesday, August 1, 2012 12:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:05 PM
Points: 287, Visits: 810
Substring cannot take negative values for the length argument.
Post #1338749
Posted Wednesday, August 1, 2012 9:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 6:49 AM
Points: 34, Visits: 250
substring ("Archive_Full_20120731200002",1, (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2))

Then how come the above expression is working where the exact string is used instead of a variable and still negative value is accepted!!! Does it make sense ?
Post #1338936
Posted Thursday, August 2, 2012 12:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 5,317, Visits: 12,351
Works for me. I suspect that you may have got your variable syntax wrong. Here is my version:

Substring (@[User::TestString],1, (FINDSTRING(@[User::TestString] ,"Full_", 1)-2))




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1338970
Posted Thursday, August 2, 2012 7:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:05 PM
Points: 287, Visits: 810
Devesh_Srivastava (8/1/2012)
substring ("Archive_Full_20120731200002",1, (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2))

Then how come the above expression is working where the exact string is used instead of a variable and still negative value is accepted!!! Does it make sense ?


Good question. I only know that when attempting to put in a negative value for length in a much simpler expression, it errored on negative value of length parameter.



Post #1339177
Posted Thursday, August 2, 2012 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 5,317, Visits: 12,351
herladygeekedness (8/2/2012)
Devesh_Srivastava (8/1/2012)
substring ("Archive_Full_20120731200002",1, (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2))

Then how come the above expression is working where the exact string is used instead of a variable and still negative value is accepted!!! Does it make sense ?


Good question. I only know that when attempting to put in a negative value for length in a much simpler expression, it errored on negative value of length parameter.



The result of

FINDSTRING( "Archive_Full_20120731200002","Full_", 1) -2

is 7. That's not a negative value where I come from



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1339193
Posted Thursday, August 2, 2012 8:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:05 PM
Points: 287, Visits: 810
Not talking about the result, talking about the length argument.

I created a simple expression using -2 as length and was unable to save the expression due to "length cannot accept negative number" (okay, not a direct quote, but teh error was specific and when I changed length to positive number, no issues).


Post #1339235
Posted Thursday, August 2, 2012 9:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 5,317, Visits: 12,351
herladygeekedness (8/2/2012)
Not talking about the result, talking about the length argument.

I created a simple expression using -2 as length and was unable to save the expression due to "length cannot accept negative number" (okay, not a direct quote, but teh error was specific and when I changed length to positive number, no issues).




??

The expression I quoted was only the length argument - from the OP's example.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1339262
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse