1. What is the equivilent of Access's VAL in SQL?

Example: "EQ12345" becomes 12345 (and is a number)

2. Also, is there a different/better way of converting the string "98765" to 98765 (a number)?

Thanks!

__________________
Mitch

1. There isn't one. (P.S. I didn't think 'val()' would convert 'EQ12345' as you indicate. I thought it stopped as soon as it encountered a nonnumeric character, which would be the first one). In any case, I don't know a good way to do what you want, except to parse off the string one character at a time and build another with just the numeric characters via LIKE, then convert the string to numeric. This is, of course, procedural and ugly in SQL.

2. To convert the string '98765' to an integer, use CAST, eg. CAST('98765' as integer)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Bingo! :)

I was actually using: Val(Mid([OrigID],3)) which returned a string version of the number then VAL converted it to an actual number. I glossed over what the code was actually doing.

Now I remember that VAL dosen't strip out the letters. (Althought, that would be neet function.)

I actually was using CAST elswhere so I kind of know how to use it.

Thanks!