Study Notes - Sql Intro

Intro to Transact-SQL

Convert between data types

Cast & Try_Cast

Convert & Try_Convert

Convert between data types

Cast & Try_Cast

SELECT CAST(Size AS integer) As NumericSize FROM Production.Product; => Conversion failed when converting the nvarchar value 'M' to data type int

SELECT TRY_CAST(Size AS integer) As NumericSize FROM Production.Product; => a numeric data type are returned as decimal values, and the incompatible values are returned as NULL

Convert & Try_Convert

Parse & Try_Parse

The PARSE function is designed to convert formatted strings that represent numeric or date/time values.

Str

Converts a numeric value to a varchar . STR(number, length, decimals)

SELECT [UserId], '$' + str([UserId]) Result1, '$' + str([UserId],1, 1) Result2,'$' + cast([UserId] as varchar(10)) Result3 FROM [CarHire].[dbo].[CarHires]

UserId Result1 Result2 Result3

9 $ 9 $9 $9

Handle NULLS

ISNULL

The ISNULL function takes two arguments. The first is an expression we are testing. If the value of that first argument is NULL, the function returns the second argument

COALESCE

It will return the first expression in the list that is not NULL.

SELECT COALESCE( expression1, expression2, [ ,...n ] )

NULLIF

NULLIF replaces a value of 0 with a NULL. It returns the value if it is not 0

SELECT NULLIF(4, 0) AS Result1 --> 4

SELECT NULLIF(0, 0) AS Result2 --> NULL

Modify Data

Merge

MERGE INTO Sales.Invoice as i

USING Sales.InvoiceStaging as s

ON i.SalesOrderID = s.SalesOrderID

WHEN MATCHED THEN

UPDATE SET i.CustomerID = s.CustomerID,

i.OrderDate = GETDATE(),

i.PurchaseOrderNumber = s.PurchaseOrderNumber,

i.TotalDue = s.TotalDue

WHEN NOT MATCHED THEN

INSERT (SalesOrderID, CustomerID, OrderDate, PurchaseOrderNumber, TotalDue)

VALUES (s.SalesOrderID, s.CustomerID, s.OrderDate, s.PurchaseOrderNumber, s.TotalDue);

Page updated

Google Sites

Report abuse