Sorting nvarchar field in CR9

[SuperTime]

I am trying to sort a nvarchar field type with numbers/id's
Field Name : invoice #
Eg Data: 1132 Old DAY QB, 18213, 18214, 18215.....3770, 3771,37702

1)I wan't to first trim out the charcters on right and keep just the numbers
2) Then I would like to convert this in to a numeric field and sort it in ascending order

I came accross a similar thread:
help requested with sorting by a nvarchar field in CR8
thread766-857486

But the problem is I am using CR9 and for some strange reason I cannot see the Option To Create A SQL Expression...I was wondering if there is an option to enable it..??

Please Advice..


Thanks.

 

[synapsevampire]

Post your database type, connectivity and type of databse object used as the data source.

Some databases aren't SQL databases, and some connectivity types disallow it, and if you're using a Stored Procedure, obviously you couldn't use one.

And is your example data one record of one field, or the contents of numerous rows, or numerous fields, or?

-k

 

[lbass]

If the numbers are always to the left, create a formula:

val({table.string})

Then use this for sorting and display.

-LB