Rob Farley

Rob Rob Farley has been consulting in IT since completing a Computer Science degree with first class honours in 1997. Before moving to Adelaide, he worked in consultancies in Melbourne and London. He runs the development department in one of Australia's leading IT firms, as well as doing database application consultancy and training. He heads up the Adelaide SQL Server User Group, and holds several Microsoft certifications.

Rob has been involved with Microsoft technologies for most of his career, but has also done significant work with Oracle and Unix systems. His preferred database is SQL Server and his preferred language is C#. Recently he has been involved with Microsoft Learning in the US, creating and reviewing new content for the next generation of Microsoft exams.

Over the years, Rob's clients have included BP Oil, OneLink Transit, Accenture, Avanade, Australian Electorial Commission, the Chartered Institute of Personnel and Development, the Royal Borough of Kingston, Help The Aged, Unisys, Department of Treasury and Finance (Vic), National Mutual, the Bible Society and others.

Did you mean to come here? My blog is now at http://msmvps.com/blogs/robfarley



15 September 2006

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.