Published
Monday, August 27, 2007 3:41 PM
by
mtaulty
Following on from a previous post and looking at the reference for Entity SQL, there's a lot that's familiar. Quickly copying the keywords out of the help file there are unary and binary operators such as;
- (Negative)
- (Subtract)
!= (Not Equal To)
% (Modulo)
* (Multiply)
/ (Divide)
+ (Add)
=+ (String Concatenation)
< (Less Than)
<= (Less Than or Equal To)
<> (Not Equal To)
= (Equals)
> (Greater Than)
>= (Greater Than or Equal To)
And items from the standard set of maths and aggregate functions you’d expect to find;
ABS, ACOS, ASCII, ASIN, ATAN, ATN2, AVG, CEILING, COS, COUNT, COUNT_BIG, DEGREES, EXP, FLOOR, LOG, LOG10, MAX, MIN, RADIANS, RAND, SIGN, SIN, SQRT, SQUARE, STDEV, PI, POWER, ROUND, SUM, SUMSTRING (not sure exactly what this does), TAN, VAR, VARP
And string, character related functions such as;
CHAR, CHARINDEX, DIFFERENCE (see SOUNDEX), LEN, LIKE, LOWER, LTRIM, PATINDEX, REPLACE, REPLICATE, REVERSE, RTRIM, SOUNDEX (arguably not about strings but it sort of is :-)), SPACE, STUFF, SUBSTRING, UPPER
Then there’s date related bits and pieces;
CURRENT_TIMESTAMP, DATEADD, DATEDIFF, DATENAME, DATEPART, DAY, GETDATE, GETUTCDATE, MONTH, YEAR
Then there’s bits of SQL that you’d expect to see and a few odds and ends that I’ve loosely bundled together here to avoid me creating lots of categories;
AND, BETWEEN, CASE, CAST, CHECKSUM, CHECKSUM_AGG, COLLATE, DATALENGTH, ELSE, GROUP BY, HAVING, IN, IS [NOT] NULL, EXCEPT, EXISTS, FROM, INTERSECT, NEWID, NOT, OR, ORDER BY, THEN, UNION, WHEN
And, then, there’s this little lot listed below which are different in a big way. What the heck do these do? I thought I'd try and come up with examples of them all to see what they do and perhaps bundle those into separate posts (on the basis that more posts are better than one massive post - someone let me know sometime if that's not the right way to go).
- ANYELEMENT
- CREATEREF
- DEREF
- IS OF
- KEY
- MULTISET
- NAVIGATE
- OFTYPE
- OVERLAPS
- Projection (including here as I've not yet figured out what it is :-)).
- REF
- ROW
- SELECT (including here because it has some weirdness like VALUE)
- SET
- TREAT
- USING