snippetsqlModerate
How to use variables inside a select (SQL Server)?
Viewed 0 times
sqlvariableshowserverselectuseinside
Problem
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:
How do I avoid writing this twice without using more than one select?
Example:
SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
,hashbytes('SHA1', LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com') as HashedEmail
FROM sometableHow do I avoid writing this twice without using more than one select?
Solution
Derived table
or CTE:
However, in this case I'd consider using a computed column with the hash precalculated
SELECT
EMail,
hashbytes('SHA1', EMail) AS HashedEmail
FROM
(
SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
FROM sometable
) fooor CTE:
;WITH cEMail AS
(
SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
FROM sometable
)
SELECT
EMail,
hashbytes('SHA1', EMail) AS HashedEmail
FROM
cEMailHowever, in this case I'd consider using a computed column with the hash precalculated
Code Snippets
SELECT
EMail,
hashbytes('SHA1', EMail) AS HashedEmail
FROM
(
SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
FROM sometable
) foo;WITH cEMail AS
(
SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail
FROM sometable
)
SELECT
EMail,
hashbytes('SHA1', EMail) AS HashedEmail
FROM
cEMailContext
StackExchange Database Administrators Q#4169, answer score: 13
Revisions (0)
No revisions yet.