Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If I want to compute a column and use the result in more than 1 column, how do I do this without making the computing twice?

Example:

SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
      ,hashbytes('SHA1', LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com') as HashedEmail
FROM sometable

How do I avoid writing this twice without using more than one select?

share|improve this question

2 Answers 2

up vote 11 down vote accepted

Derived table

SELECT
    EMail,
    hashbytes('SHA1', EMail) AS HashedEmail
FROM
    (
    SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
    FROM sometable
   ) foo

or CTE:

;WITH cEMail AS
(
    SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
    FROM sometable
)
SELECT
    EMail,
    hashbytes('SHA1', EMail) AS HashedEmail
FROM
    cEMail

However, in this case I'd consider using a computed column with the hash precalculated

share|improve this answer
    
+1 for the computed column. –  Cape Cod Gunny Jul 28 '11 at 17:07
    
As long as the domain is static and dynamic per-request, the computer column is a terrific idea. –  Robert Miller Jul 29 '11 at 4:15

First, I think you wanted to write hashbytes instead of haststring. hashstring is no SQL function.

Here is the code that will give you want you want I hope.

DECLARE @yourvariable nvarchar (50) =  '@somedomain.com';

SELECT LOWER(SUBSTRING([NAME], 4, 100)) + @yourvariable  as EMail
      ,HashBytes('SHA1', LOWER(SUBSTRING([NAME], 4, 100)) +  @yourvariable) as HashedEmail 
FROM sometable;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.