Mohamed Ahmed Mohamed Ahmed - 22 days ago 7
SQL Question

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))

Answer

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.

Comments