need help, How to change datatype in order to sort some extracted numbers from string

Hello,

I am trying to extract from some strings like the following strings the number and order them by that number:

Box 1
Box 2
Box 3
Box 20
Box 21
...(and so on)

The problem I am having is that I already extracted the number using

Substring([field],[starting position],[lenght])

but the output seems to be in a string format, so the order is not in an ascending order.

Thanks for any suggestions.

You need to cast the result as a number. It looks like your number is always an integer so the following should do it:

(DT_I4)Substring([field],[starting position],[lenght])

-Jamie

|||I tried it but I am getting an SQL Execution Error message,

I am doing this on the Query Builder using Visual Studio 2005.net

|||

If you're getting an error message its useful to post it up here!

-Jamie

|||Sorry, I am behind a very restrictive firewall at work, doesnt let me get inthere!

Why should I post it somewhere else, anyways?

|||

mendez_edd wrote:

Sorry, I am behind a very restrictive firewall at work, doesnt let me get inthere!

It doesn't let you get to where? Can you replicate the error in BIDS?

mendez_edd wrote:

Why should I post it somewhere else, anyways?

Posting the error message helps people deduce what is causing the error!

-Jamie

|||

Where are you doing this? Jamie’s solution was for a SSIS expression as you might use in the Derived Column Transform. Your did mention a SQL exception, but since this is a SSIS forum, we are kind of thinking you may want a SSIS solution.

To sort like a number, convert the value to a number, so strip off the text as Jamie suggested, or pad the numeric part with spaces ans sort as a string–
Box 1
Box 2
Box 21
Box 22

(Note the extra space for single digit numbers.)

Regardless of your firewall you can post to this forum, so could you not type the error message text you get into a post?