CODEWITHSUNDEEP
sqlsql-servert-sql
mshwf
mshwf

Reputation: 7449

How to set variable value from column data?

I want to declare a variable for reusability for this query:

SELECT 
    SUBSTRING(Email, CHARINDEX('@', Email) + 1, LEN(Email) - CHARINDEX('@', Email)), 
    COUNT(*) 
FROM 
    Student
GROUP BY 
    SUBSTRING(Email, CHARINDEX('@', Email) + 1, LEN(Email) - CHARINDEX('@', Email))

The variable holds SUBSTRING(Email,CHARINDEX('@', Email)+1,LEN(Email)-CHARINDEX('@', Email))

But it seems that the column data is not reachable outside SELECT statement:

DECLARE @exp NVARCHAR(20) =    
   SUBSTRING(Email,CHARINDEX('@', Email)+1,LEN(Email)-CHARINDEX('@', Email))

Upvotes: 1

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

One method of doing this uses a view; another uses a CTE. I happen like using OUTER APPLY because it makes it easy to add lots of variables that refer to each other:

SELECT domain, COUNT(*)
FROM Student s OUTER APPLY
     (VALUES ( SUBSTRING(s.Email, CHARINDEX('@', s.Email) + 1, LEN(s.Email) - CHARINDEX('@', s.Email)) )
     ) v(domain)
GROUP BY domain;

Or, you could write:

SELECT domain, COUNT(*)
FROM Student s OUTER APPLY
     (VALUES ( CHARINDEX('@', s.Email) )
     ) as pos(pos)
     (VALUES ( SUBSTRING(s.Email, pos.po + 1, LEN(s.Email) - pos.pos) )
     ) v(domain)
GROUP BY domain;

And, finally, you might find this expression easier:

SELECT domain, COUNT(*)
FROM Student s OUTER APPLY
     (VALUES ( STUFF(s.Email, 1, CHARINDEX('@', s.Email), '') )
     ) v(domain)
GROUP BY domain;

With this simplification, you might not even want an additional variable at all.

Upvotes: 2

Related Questions