Wednesday, March 13, 2013

Operators in SQL Server


Scope Resolution Operator: 
The scope resolution operator :: provides access to static members of a compound data type.
A compound data type is one that contains multiple simple data types and methods.


DECLARE @hid hierarchyid;
SELECT @hid = hierarchyid::GetRoot();
PRINT @hid.ToString();


Result: /

Bitwise Operators:

DECLARE @a int;
SET @a = 10 & 20
print @a

SET @a = 10 ^ 20
print @a

SET @a = 10 | 20

print @a

Result:
0
30
30
Explanation:

Binary Representation of 10, 20:

10 = 01010
20 = 10100

01010 & 10100 = 00000 = 0
01010 ^ 10100 = 11110 = 30
01010 | 10100 = 11110 = 30


 

Concatenating two strings:

SELECT 'FirstName' + ' ' + 'LastName' AS FullName

SELECT CAST(1 AS VARCHAR(10)) + 'R' + CAST(2 AS VARCHAR(10))

SELECT FirstName + ' ' + LastName
FROM Person.Person

Wildcards:
% - Matches any string of zero or more characters. This wildcard character can be used as either a prefix or a suffix

SELECT FirstName, LastName
FROM Person.Person
WHERE FirstName LIKE 'Dan%';
           
[] - Matches any single character within the specified range or set that is specified between the brackets.

SELECT
      e.BusinessEntityID,
      p.FirstName,
      p.LastName,
      a.PostalCode
FROM HumanResources.Employee AS e
      INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
      INNER JOIN Person.BusinessEntityAddress AS ea ON e.BusinessEntityID = ea.BusinessEntityID
      INNER JOIN Person.Address AS a ON a.AddressID = ea.AddressID
WHERE a.PostalCode LIKE '[0-9][0-9][0-9][0-9]';

[^] - Matches any single character that is not within the range or set specified between the square brackets.

SELECT FirstName, LastName
FROM Person.Person
WHERE FirstName LIKE 'Al[^a]%'

ORDER BY FirstName;

 

_ - Matches any single character in a string comparison operation that involves pattern matching, such as LIKE and PATINDEX.

SELECT FirstName, LastName
FROM Person.Person
WHERE FirstName LIKE '_an'

ORDER BY FirstName;

 


No comments:

Post a Comment