Access Val() function

Steve Koop gave a great presentation at the user-group yesterday about upsizing from Access to SQL. One of the things he mentioned was that there is no equivalent for the Val() function in SQL. To fill in the non-Access-savvy, VAL('abad2j3lk2345') = 232345. It takes any numbers in a string, and converts them into an integer.

Here's my equivalent for SQL2005. Let's start by using our old favourite, the auxiliary table of numbers. I'll assume that we're doing defining @str as a varchar(1000) or nvarchar(1000). If you prefer varchar to nvarchar, then just change the strings accordingly.

So, with the auxiliary table of numbers (which goes from 1 to 1000), we can grab each character.

SELECT num, SUBSTRING(@str,num,1) AS ch
FROM nums
WHERE num <= LEN(@str)

Great. So now let's filter this to only pick up the ones that are numbers.

AND SUBSTRING(@str,num,1) BETWEEN N'0' AND N'9'

Let's add a rownum field to the results, but reversed (you'll see why in a second).

SELECT num, SUBSTRING(@str,num,1) AS ch, ROW_NUMBER() OVER (ORDER BY num DESC) as rn
FROM nums
WHERE num <= LEN(@str)
AND SUBSTRING(@str,num,1) BETWEEN N'0' AND N'9'

But actually want I want is 10^rn, so let's do that instead. And while we're at it, let's cast that 'ch' field to an integer.

SELECT num, CAST(SUBSTRING(@str,num,1) AS INT) AS ch, POWER(10,ROW_NUMBER() OVER (ORDER BY num DESC)-1) as pwr
FROM nums
WHERE num <= LEN(@str)
AND SUBSTRING(@str,num,1) BETWEEN N'0' AND N'9'

You can see where I'm going now... let's multiply 'ch' and 'pwr', and sum them. We need to do this using a derived table, because you can't use a ranking function inside an aggregate.

SELECT SUM(ch * pwr)
FROM
(SELECT CAST(SUBSTRING(@str,num,1) AS INT) AS ch, POWER(10,ROW_NUMBER() OVER (ORDER BY num DESC)-1) AS pwr
FROM nums
WHERE num <= LEN(@str)
AND SUBSTRING(@str,num,1) BETWEEN N'0' AND N'9'
) c

So now, throw this into a user-defined function, and you have a VAL() equivalent.