Connecting Tech Pros Worldwide Help | Site Map

sql server query problem

Dean g
Guest
 
Posts: n/a
#1: Jul 21 '08

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 ***
Erland Sommarskog
Guest
 
Posts: n/a
#2: Jul 21 '08

re: sql server query problem


Dean g (big_deanus@hotmail.com) writes:
Quote:
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?
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
Dean g
Guest
 
Posts: n/a
#3: Jul 21 '08

re: sql server query problem



Thanks alot, it works perfectly

Dean


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