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


Microsoft-SQL-Server-Training-Online-Learning-Classes-Built-in-functions-Type-Conversion-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:
      1. The country formats for integer, real etc is identical
        e.g, CONVERT(VarChar(10),'1234.56', 1)= CONVERT(VarChar(10),'1234.56', 10)
      2. 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

3 comments:

  1. Great blog !It is best institute.Top Training institute In chennai
    http://chennaitraining.in/oracle-dba-training-in-chennai/
    http://chennaitraining.in/sql-server-dba-training-in-chennai/
    http://chennaitraining.in/teradata-training-in-chennai/
    http://chennaitraining.in/sap-hr-training-in-chennai/
    http://chennaitraining.in/sap-fico-training-in-chennai/
    http://chennaitraining.in/sap-abap-training-in-chennai/

    ReplyDelete
  2. Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
    sql dba training
    sql server dba online training

    ReplyDelete
  3. Investing Secne is the right resource to help traders find the best brokers, strategies and investment news online. We offer content marketing services, reviews, trading charts, and industry news so you can find what you're looking for quickly. Investing Secne is a portal created for traders, to help them find the best brokers and strategies online on a single platform.

    ReplyDelete