Tuesday, September 30, 2014

20 T-SQL Functions

This article demo some of the common T-SQL Functions.  Purple are new to SQL 2012 which will be cover in here.

  • NULL Functions
    • ISNULL
    • COALESCE
  • Date/Time Functions
    • GETDATE()
    • GETUTCDATE()
    • DATEPART()
    • DATEDIFF()
    • DATEADD()
    • ISDATE()
    • DATEROMPARTS()
    • TIMEFORPARTS()
    • EOMONTH()
    • PARSE()
  • String Functions
    • CHARINDEX()
    • PATINDEX()
    • LEFT()
    • RIGHT()
    • LTRIM()
    • RTRIM()
    • LEN()
    • CONCAT()
    • FORMAT()
  • Data Type Conversions
    • CONVERT()
    • CAST()
    • TRY_PARSE()
    • TRY_CONVERT()
  • Logical Functions
    • CHOOSE()
    • IIF()

NULL FUNCTIONs

Please see here.

Date/Time Functions

Use [AdventureWorks2014]
GO
-- DATE Related functions
SELECT GETDATE()
GO
SELECT GETUTCDATE()
GO
SELECT 
    DATEPART(year, GETDATE() ) AS 'YEAR',
    DATEPART(month,  GETDATE()) AS 'MONTH',
    DATEPART(day,  GETDATE()) AS 'DAY',
    DATEPART(dayofyear,  GETDATE()) 'DAT OF THE YEAR',
    DATEPART(weekday,  GETDATE()) AS 'WEEK DAY'
GO
 
SELECT DATEDIFF(day, '2010/6/11', GETDATE());
GO
SELECT DATEADD(month, 1, GETDATE());
GO
IF ISDATE('2009-05-12 10:19:41.177') = 1
    PRINT 'VALID'
ELSE
    PRINT 'INVALID';
GO
IF ISDATE('I AM STRING') = 1
    PRINT 'VALID'
ELSE
    PRINT 'INVALID';

image

ISDATE() is used to validate whether input string is valid date or not.

image

String Functions

  • CHARINDEX(): Returns the starting position of the first occurrence of a string in a specified expression, or zeros if the string is not found, on all valid text and character data types.
  • PATINDEX(): Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
  • LEFT(): Returns the left part of a character string with the specified number of characters.
  • RIGHT(): Returns the right part of a character string with the specified number of characters.
  • LTRIM(): Remove the leading blank
  • RTRIM(): Remove the blank in the end of string
  • LEN(): show the length of the sting
DECLARE @document varchar(64);
SELECT @document = 'Happy Birthday everyone.';
SELECT CHARINDEX('Birthday', @document);
SELECT PATINDEX('%Bir%', @document);
SELECT LEFT(@document, 6)
SELECT RIGHT(@document, 6)
SELECT LTRIM(@document)
SELECT RTRIM(@document)
SELECT LEN(@document)
GO

image

Data Type Conversions

  • CONVERT()
  • CAST()
  • PARSE()
SELECT CONVERT(datetime2,'12/31/2010') AS Result;
GO
SELECT CAST('12/31/2010' AS datetime2) AS Result;
GO
SELECT PARSE('12/31/2010' AS datetime2 USING 'en-US') AS Result;
GO
image

For CONVERT, CAST and PARSE, if converting data type fail, it will generate the error message. In SQL 2012, new functions TRY_CONVERT(), TRY_PARSE() has been introduce, if convention fail, it will return NULL .

20 comments:

  1. Great blog created by you. I read your blog, its best and useful information. You have done a great work. Super blogging and keep it up.php jobs in hyderabad.

    ReplyDelete
  2. I was surfing the Internet for information and came across your blog. I am impressed by the information you have on this blog. It shows how well you understand this subject. campingplatz holland mit hund

    ReplyDelete
  3. Such a very useful article. Very interesting to read this article. I would like to thank you for the efforts you had made for writing this awesome article.
    Data Science Course in Pune
    Data Science Training in Pune

    ReplyDelete
  4. Nice blog. I finally found great post here Very interesting to read this article and very pleased to find this site. Great work!
    Data Science Training in Pune
    Data Science Course in Pune

    ReplyDelete
  5. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    Data Analytics Course in Pune
    Data Analytics Training in Pune

    ReplyDelete
  6. Thumbs up guys your doing a really good job. It is the intent to provide valuable information and best practices, including an understanding of the regulatory process.
    Cyber Security Course in Bangalore

    ReplyDelete
  7. Very nice blog and articles. I am really very happy to visit your blog. Now I am found which I actually want. I check your blog everyday and try to learn something from your blog. Thank you and waiting for your new post.
    Cyber Security Training in Bangalore

    ReplyDelete
  8. I am impressed by the information that you have on this blog. It shows how well you understand this subject.
    Best Data Science Courses in Bangalore

    ReplyDelete
  9. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
    Ethical Hacking Course in Bangalore

    ReplyDelete
  10. Wow! Such an amazing and helpful post this is. I really really love it. I hope that you continue to do your work like this in the future also.
    Ethical Hacking Training in Bangalore

    ReplyDelete
  11. I am impressed by the information that you have on this blog. Thanks for Sharing
    Ethical Hacking in Bangalore

    ReplyDelete
  12. Here at this site really the fastidious material collection so that everybody can enjoy a lot.

    Data Science Course

    ReplyDelete
  13. Your work is very good and I appreciate you and hopping for some more informative posts.

    Data Science Training

    ReplyDelete
  14. Terrific post thoroughly enjoyed reading the blog and more over found to be the tremendous one. In fact, educating the participants with it's amazing content. Hope you share the similar content consecutively.

    artificial intelligence course in bhilai

    ReplyDelete
  15. Speaking honestly this blog is absolutely amazing in learning the subject that is building up the knowledge of every individual and enlarging to develop the skills which can be applied in to practical one. Finally, thanking the blogger to launch more further too.

    Data Science training in Bhilai

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates