SqlTutorial-1 : Lesson-11 : Class-3 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Built-in Sql functions: Type Conversion and Null Checking

INDEX

1. Data-Type Converting functions

1. CAST()
2. CONVERT()

2. Null-Type Checking functions

1. ISNULL()
2. NULLIF()
3. COALESCE()

1. Data Conversion functions (2 methods)

1. CAST():

   Converts one type to another type.

   Syntax:

   CAST(expression AS data_type)

   Examples

   1. Casting to Date:

      SQL Server interprets two digit years as:
      If the year < 49 => formatted as 20XX; If the year >49 => formatted as 19XX;
      
      
      1.SELECT CAST('11/11/72' AS smalldatetime) => 1972-11-11 00:00:00
      2.SELECT CAST('6/5/40' AS smalldatetime) => 2040-06-05 00:00:00

   2. IMPLEMENTING DATE DIFFERENCE

      select CAST('2002-10-10' AS datetime)
                     – CAST('2002-09-10' AS datetime) AS int) => 30

   3. Simple char to integer conversion.

      SELECT CAST('123' AS Int) => 123

   4. Char to Floating point conversion.

      SELECT CAST('123.4' AS Decimal(9,2)) => 123.40

2. Convert()

   Converts one type of data to another.

   Syntax:

   CONVERT(data_ type(<length>), expression, <style>)
   The length option is only used on the following data types: nchar, char, varchar, nvarchar, varbinary, and binary.

   Examples:

   1. Converting date to varchar with various country formats.

      SELECT CONVERT(varchar, getdate(), 1) => 06/02/09
      SELECT CONVERT(varchar, getdate(), 2) => 09.06.02
      SELECT CONVERT(varchar, getdate(), 3) => 02/06/09
      SELECT CONVERT(varchar, getdate(), 4) => 02.06.09
      SELECT CONVERT(varchar, getdate(), 5) => 02-06-09
      SELECT CONVERT(varchar, getdate(), 6) => 02 Jun 09
      
      

      Table: Values for the style argument of CONVERT() for various countries.

      Style No. without century(YY)	Style No. with Century (yyyy)	Output Type For	Format
      -	0 or 100	Default	MON DD YYY
      1	101	USA	MM/DD/YYYY
      2	102	ANSI	YYYY.MM.DD
      3	103	British / French	DD/MM/YYYY
      4	104	German	DD.MM.YYYY
      5	105	Italian	DD-MM-YYYY
      6	106	-	DD MON YYYY
      7	107	-	MON DD, YYYY
      10	110	USA	MM-DD-YY
      11	111	JAPAN	YY/MM/DD
      12	112	ISO	YY MM DD
      20	120	ODBC Canonical	YYYY-MM-DD hh:mi:sec
      Notes: The country formats for integer, real etc is identical e.g, CONVERT(VarChar(10),'1234.56', 1)= CONVERT(VarChar(10),'1234.56', 10) The default format SQL uses to store the date is yyyy-dd-mm

   2. Converting varchar to date.(it takes any format of date)

      SELECT CONVERT(datetime, '20000604') =>2004-06-04 12:10:00
      SELECT CONVERT(datetime, '2 Jun 2004') =>2004-06-02 12:10:00
      SELECT CONVERT(datetime, ' Jun 2 2004') =>2004-06-02 12:10:00
      SELECT CONVERT(datetime, ' 2-jun- 2004') =>2004-06-02 12:10:00

   3. Converting money to char:

      Like date, type money is formatted differently in different countries as date.
      SELECT 'Money' = advance,
            'Varchar' = CONVERT(varchar(10), advance),
            'Varchar-1' = CONVERT(varchar(10), advance, 1),
            'Varchar-2' = CONVERT(varchar(10), advance, 2)
      

      Money	Varchar	Varchar-1	Varchar-2
      8000.0000	8000.00	8,000.00	8000.0000
      5000.0000	5000.00	5,000.00	5000.0000
      4000.0000	4000.00	4,000.00	4000.0000
      2000.0000	2000.00	2,000.00	2000.0000
      0.0000	0.00	0.00	0.0000

   4. converting char to money.

      SELECT CONVERT(money, '8,000.00', 1) =>8000.0000

   5. converting char to integer

      SELECT CONVERT(INTEGER,'123')=>123
      //Note if CONVERT(INTEGER,’1A’)=> ERROR.

   6. Converting char to decimal.

      SELECT CONVERT(Decimal(9,2), '123.4')=> 123.40

   7. String comparison for checking date equality.

      SELECT * FROM Orders
      WHERE CONVERT(varchar(12), OrderDate, 101)
                 = CONVERT(varchar(12), GETDATE(), 101)

Null-Type checking functions

1. ISNULL():

   Determins whether or not it is null

   Syntax:

   ISNULL(check_expression, replacement_value)

   Examples

   1. Replacing NULL values with ZERO values

      SELECT ISNULL(qty,0) AS qty
      FROM Orders;

   2. Replacing NULL values with current date.

      SELECT ISNULL(OrderDate, GETDATE())

   3. Verifying ISNULL() returns given value if varible contains null;

      SELECT ISNULL(NULL,’YES’)=> YES

2. NULLIF()

   Returns NULL if both values are equal otherwise returns first expression.

   Syntax:

   NULLIF(exp1,exp2);

   Examples:

   1. SELECT NULLIF(2,2)=>NULL
   2. SELECT NULLIF(2,3)=> 2

   3. No need expressions to have same type.

      SELECT NULLIF(2,’2’) => NULL
   4. SELECT NULLIF(2,’4’) =>2

3. COALESCE()

   Getting first not null value from list.; returns the first Non-NULL value in a list of expressions. It is equivalent to CASE expression but works with NULL values.

   Syntax:

   COALESCE(expression1, expression2, ... expressionN)
   1. SELECT COALESCE (NULL, 10, NULL) => 10
   2. SELECT COALESCE (NULL, 10, 20) => 10
   3. SELECT COALESCE (5, 10, NULL) => 5
   4. SELECT COALESCE (NULL, '10', 30) => 10
   1. If all arguments are NULL, COALESCE returns NULL.
   2. COALESCE(expression1,...n) is equivalent to the following CASE expression:
      CASE WHEN (expression1 IS NOT NULL) THEN expression1
               WHEN (expression2 IS NOT NULL) THEN expression2
                ...
               ELSE expressionN
         END