SQL Value Functions

3 Key excerpts on "SQL Value Functions"

• 

  eBook - PDF

  SQL: 1999

  Understanding Relational Language Components

  • Jim Melton, Alan R. Simon(Authors)
  • 2001(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  We first see that one alternative for a valid set function is simply COUNT (*), which we demonstrated for you in section 5.8.1. The next alternative is the general set function, which is initiated by a keyword indicating which sort of computation is being requested (you've seen most of these in sections 5.8.1 through 5.8.5), followed by a value expression--option- ally preceded by either DISTINCT or ALL---enclosed in parentheses. The keywords that you haven't seen yet are EVERY, ANY, and SOME. If you specify one of these keywords in a set function, then the data type of the parenthesized value expression must be a Boolean type. The value computed by EVERY will be 5.9 Value Functions 143 True if and only if the value of the expression in every row is True. The value computed by either ANY or SOME (they are synonyms here) will be True if the value of the expression in at least one row is True. Finally, let's look at the final alternative, the grouping operation. You can spec- ify this alternative only if the query in which it's specified is a grouped query, which we will consider in Chapter 9, Advanced SQL Query Expressions, at which time we'll come back to the grouping operation alternative for set func- tions. We now look at some other useful functions of SQL: 1999, starting with value functions. Value Functions Those readers familiar with commercial database management software, partic- ularly those packages used on personal computers, are accustomed to a wide variety of functions used for string manipulation, date-related tasks, and other such facilities. SQL:1999 contains four types of value functions, designed to help you perform these necessary chores within your programs. These four cate- gories are 9 numeric value functions 9 string value functions 9 datetime value functions 9 interval value functions 5.9.1 Numeric Value Functions Numeric value functions always return a numeric value, though they may oper- ate on other types of data.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Learn T-SQL From Scratch

  An Easy-to-Follow Guide for Designing, Developing, and Deploying Databases in the SQL Server and Writing T-SQL Queries Efficiently

  • Brahmanand Shukla(Author)
  • 2021(Publication Date)
  • BPB Publications

    (Publisher)

  T-SQL is a strong tool. But just like other tools, it has no meaning unless the person using it knows how to use it. Built-in functions are one of such useful features of SQL Server which gives more out of just a plain raw data. You can play with your data and generate meaningful analytics out of it with the help of these functions. There are many such features that we’ll talk about gradually as we’ll move ahead.

  Structure

  In this chapter, we will cover the following topics:

  • Aggregate functions
  • String functions
  • Numeric functions
  • Date functions

  Objective

  After studying this chapter, you’ll be able to work with the various built-in functions ranging from aggregate functions used for aggregation, string functions used with the string’s values, the numeric function used with the numeric values, and date functions used with the date values. These string, numeric, and date values could be a static value or a constant, a variable, or a column. By the end of this chapter, you’ll be able to implement these built-in functions.

  Aggregate functions

  Before we understand the aggregate functions, let’s first understand what a function is: A function is a block of organized and reusable code used to perform predefined actions and return a value.

  For example, if you want the addition of two values then you can create a function to accept two input parameters value 1 and value 2 and the function will perform value 1 + value 2 and return the result. Suppose you supplied value 1 as 100 and value 2 as 200 then the function will return the result as 100 + 200 = 300

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  SQL Interview Questions

  A complete question bank to crack your ANN SQL interview with real-time examples

  • Prasad Kulkarni(Author)
  • 2019(Publication Date)
  • BPB Publications

    (Publisher)

  In this chapter, we have seen the different SQL functions--configuration functions will help us to return information about the current configuration, conversion functions will help us in casting and converting of support datatypes, aggregate functions will help us to perform calculation of a set of values, mathematical functions will help us to perform logical functions, metadata functions will help us to get information about database objects, security functions will help us to fetch all the information related to users and their roles can be retrieved with this function. String functions will help us to play with string and its different operations.

  Additionally, we have learnedthe different SQL wildcards that can be used to fetch the rows as per the applicable pattern.

  In the upcoming chapters, we will learn more about the remaining SQL functions likedate related functions, text_images functions, and some remaining SQL operators, like compound and unary operators. Additionally, we will have a deep dive through different SQL JOINS and SQL CASE statements. So, stay tuned.

  Rapid fire questions and answers

  1. What is SQL function? SQL function is a standard set of code that can be reused and it helps us to remove code redundancy.
  2. What different types of SQL functions exist?
     The following types of SQL functions exist. Configuration functions, conversion functions, cursor functions, aggregate functions, JSON functions, logical functions, mathematical functions, metadata functions, security functions, string functions, system functions, and system statistical functions.
  3. What is aggregate function?
     Aggregate functions are used to perform calculation on a set of values; they either return float or integer value. These functions are mostly used with GROUP BY , and SELECT clauses. The examples of these functions are SUM , AVG , and more.
  4. What is the usage of configuration function?
     These functions are used to return information about the current configuration. The examples of these functions are @@DATE_FIRST , @@DBTS , @@LANGUAGE , and many more.
  5. What are wildcards in SQL?
     Wildcards are a substitute to any character. These cards are used with the LIKE operator in SQL, and are used to check if any string matches the given pattern. If it does, then it will fetch that matched records from the database. SQL has the following wildcards--%, _(underscore), [] (range).

  Sign up to read

  Learn more about book