SQL Value Functions

3 Key excerpts on "SQL Value Functions"

• 

  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)

  So, the objective of the chapter is to learn about the different SQL functions, including configuration function, conversion functions, cursor functions, date and time datatypes and functions, JSON functions, logical functions, mathematical functions, metadata functions, security functions, string functions, system functions, system statistical functions, text and image functions, and many more. We will also learn about the SQL wildcards and their usage with examples.

  So, let’s begin.

  SQL functions

  Like other programming languages, SQL Server also has its own built-in functions, (function is a standard set of code that can be reused, and it helps us to remove code redundancy). SQL allows you to create/customize your own function. SQL built-in functions can be categorized as follows:

  Function category	Description
  Configuration functions	Functions of these categories are used to return information about the current configuration.
  Conversion functions	Functions of these categories are used for casting and converting of support datatype.
  Cursor functions	Functions of these categories are used to return cursor information.
  Aggregate functions	Functions of these categories are used to perform calculation of a set of values
  Date and time datatypes and functions	Functions related to date and time belong to this category
  JSON functions	These functions are used to validate JSON data.
  Logical functions	Functions of these categories are used to perform logical operations.
  Mathematical functions	These functions are used to perform logical functions.
  Metadata functions	The information about the database and database objects can be retrieved with these functions.
  Security functions	Functions of these categories are used to fetch all the information related to users, and their roles can be retrieved using this function.
  String functions	Functions of these categories are used to play with string and its different operations.
  System functions	Functions of these categories are used to perform operations related to an instance of SQL Server.
  System statistical functions

  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 Pocket Primer

  • Oswald Campesato(Author)
  • 2022(Publication Date)
  • Mercury Learning and Information

    (Publisher)

  multiple-row functions that return one result per set of rows. Specifically, single-row functions in SQL will

  • manipulate data items
  • accept arguments and return one value
  • act on each row that is returned
  • return one result per row
  • may modify the data type
  • can be nested
  • accept arguments that can be a column or an expression

  SQL Character Functions

  There two types of character functions: case-manipulation functions and character manipulation functions. Case manipulation functions in SQL include the following:

  • LOWER
  • UPPER
  • INITCAP

  Character manipulation functions in SQL include the following built-in functions:

  • SUBSTRING
  • LENGTH
  • INSTR
  • LPAD | RPAD
  • TRIM
  • REPLACE

  Following this section are some one-line examples of some of the preceding built-in functions, where you need to replace my_table with a suitable table name and replace fname and phone_number with attributes from your table in your database:

  Remove leading spaces:

  SELECT LTRIM(fname) from my_table;

  Remove leading and trailing spaces:

  SELECT TRIM(fname) from my_table;

  Replace “-” with a space (“ ”):

  SELECT fname, REPLACE(phone_number, '-', ' ') as p_number FROM my_table;

  SQL supports built-in number functions, include the following functions:

  • ROUND
  • TRUNC
  • MOD

  An example of the built-in truncate() function (which is different from the TRUNCATE keyword) is as follows:

  -- the value 12.345 is replaced with 12:

  SELECT TRUNCATE (average, 0) from my_table;

  String Operators in SQL

  SQL supports the following string operators that perform the concatenation of strings and partial matches of strings against meta characters:

  Sign up to read

  Learn more about book