8

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?

13

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

1
  • 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
3

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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