sql server query problem

Hi,
I'm trying to use the result from the select within the same query.
See code

SELECT accountnum, char(ASCII(SUBSTRING(accountnum, 1, 1))) as
firstChar from questions where firstchar='m'

Is it possible to use the result from char(ASCII(SUBSTRING(accountnum,
1, 1))) within the same query without any nested loops, like i'm trying
to use it?

Thanks,
Dean

*** Sent via Developersdex http://www.developersdex.com ***

Dean g (big_deanus@hotmail.com) writes:Use a derived table:

SELECT accountnum, firstchar
FROM (SELECT accountnum, substring(accountnum, 1, 1) AS firstchar
FROM questions) AS s
WHERE firstchar = 'm'

In SQL 2005 you can also use a common-table expression (CTE):

WITH extract AS (
SELECT accountnum, substring(accountnum, 1, 1) AS firstchar
FROM questions
)
SELECT accountnum, firstchar
FROM extract

This may look a little clunky, but keep in mind that SQL Server will
compute it more directly.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks alot, it works perfectly

Dean


*** Sent via Developersdex http://www.developersdex.com ***