The below is a list of all of the articles on SQL Server Functions that have been written on this blog.
SQL Server – Parsing, Casting and Conversion
Sometimes the data stores in the table in a different format in comparison to the real data type it belongs to. The date might be stored as an integer value or a string value, numerical values might be stored as a character string, the currency might be stored in number format. The reason or explanation could…
Convert Delimited Data Into Columns In SQL Server
This article addresses the conversion of the delimited data into columns in SQL Server. There are two functions called “STRING_SPLIT” and “PARSENAME” that helps us convert the delimited data into a single and multiple columns. Consider the below data set for practice.Data1456, ConvertedData1, SomeOtherData11466, ConvertedData2, SomeOtherData21476, ConvertedData3, SomeOtherData4Let’s create the table and load data in it.–…
SQL Server – CASE Function- Examples
This article talks about an equivalent of Oracle SQL’s DECODE function in SQL Server. There is no such function in SQL Server but through CASE function we can construct the same expression. (Update: IIF can be used as an alternative since SQL Server 2012 onwards) CASE function evaluates a list of conditions and returns one…
SQL Server – Replicate Function
SQL Server “Replicate” Function repeats a character expression to a specified number of times. It is equivalent to or alternative of Oracle’s RPAD function. Let’s’ check how this will be useful to the developers. SELECT LEN(REPLICATE(2, ’12’)) The above query will return 12 as a result due to 2 is called 12 times (222222222222) hence…
Aggregate Functions in Analytic Context
The functions SUM, AVG, COUNT, MIN, and MAX are well-known aggregate functions that we use every day. They are to compute/summarize the multiple rows by grouping them and provide a single summary value. However, when it comes to analysis, the aggregate functions carry out twofold responsibility: The same aggregate function which computes on each row…
Apache Hive Aggregate Functions – Part-1
In both traditional relational systems and big data technologies such as Apache Hive and Apache Impala, most of the functions specified in this series are common. An aggregate function or aggregation function in database management is a function where the values of multiple rows are grouped together to form a single summary value. The dataset…
Apache Hive Aggregate Functions – Part-2
This is continuation part of “Apache Hive Aggregate Functions” Function: VARIANCE & VAR_POP Returns the population variance of the total number of records present in the specified column. Syntax: variance(col), var_pop(col) Example: SELECT VARIANCE(amount) FROM tbSalesData; The result is 14618.639 As per the calculation, the variance supposed to be 15836.858 however in Apache Hive 1.1.0,…
An alternative to ISNULL() and NVL() functions in Hive
The NVL() function enables you to substitute null for a more relevant alternative in the query results. This function accepts two arguments. If the first argument is null, then it returns the second argument. If the first argument is not null, it returns the first one and will ignore the second argument. This function is…
TOP, LIMIT, ROWNUM vs DENSE_RANK
What would you do if you were asked to identify top-ten products based on their prices? In SQL Server, using a TOP clause with a specified number of records with descending order of price? In MySQL and Impala, using a LIMIT clause with a specified number of records with descending order of price? Basically, TOP…
Best Usage of NTILE function in SQL Server and other RDBMSs
SQL Server NTILE() is a window function that distributes rows of an ordered partition right into a unique quantity of approximately same partition or group or buckets. The use of the function is described with a real time problem scenario. Problem Scenario: An agency is doing unique sort of promotional events on 1st of each month. The administration wanted to peer, which promotional event has benefited the organization in each region. USE dbTestGO CREATE TABLE…
Hijri Date in SQL Server – with Islamic Month Name
This article focuses on converting the Gregorian date into an Islamic date along with Islamic month name in textual format. In SQL Server there are two methods to convert the Gregorian date to Islamic date, however, for Islamic month name we need to depend on our own code. Either you can write a function…
SQL Server 2016, 2017 and 2019 New Functions
There are several string and analytical functions introduced with the newer versions of SQL Server and are listed below. STRING_SPLIT is a table-valued function introduced in SQL Server 2016 (13.x) that splits a string into rows of sub-strings, based on a specified separator character. Discussed about its functionality and usage in my previous blog. Please…
Approximate Count of Unique Values in SQL Server
In my previous blog, I discussed the approximate count of unique values for which a predefined function is available in Impala (NDV), Oracle 12c (APPROX_COUNT_DISTINCT) and in MongoDB (estimatedDocumentCount()). However, I didn’t mention about SQL Server since there is no such function available in the versions I am using. However, the function has been introduced…
DISTINCT vs APPROXIMATE DISTINCT
As all (or most) of us know, SELECT DISTINCT eliminates duplicate records from the results and returns only unique values. But it’s very expensive in terms of resources consumption as it requires to sort or hash all rows over all columns in the table. The use of DISTINCT might be pleasant until the column is…
Calculating “Approximate Median” in Cloudera Impala, Apache Hive, SQL Server, Oracle and MySQL
APPROX_MEDIAN is an approximate inverse distribution function that accept a nonstop/continuous dispersion model. It takes a numeric or datetime value and returns an estimated middle value or an approximate interpolated value that would be the middle value once the values are sorted. Nulls are ignored in the calculation. In short, median is the middle value…
Word Count in SQL Server – String_Split Usage
STRING_SPLIT is a table-valued function that splits the words from the input text and returns into rows. This function has been introduced in SQL Server 2016. String_split will help in many ways. 1) You can get the word count from the given text. 2) You can pass the string values to a query. Word Count…
Computing Running & Cumulative Average
This article helps you in understanding how to calculate running average and cumulative average on a given data-set. As discussed earlier, windowing functions are the same in SQL Server, Oracle & Hive and perform computations across a set of table rows that are related to the current row either by the department, area, category,…
Concatenate rows (group concatenation) in MySQL, Hive, SQL Server and Oracle
The CONCAT() function joins the input strings or different column values and returns as a single string. However, if we need to concatenate rows of strings (rows from the same column) into a single string with a desired separator, this will not work. There comes a function that fulfills this requirement and this function known…
Json in SQL Server – Load and convert Json data
Data is the backbone of the business, and it can be in any sort of format. It can be lying in your relational databases or it can be a tweet in the twitter or a post in the Facebook or a message in the Instagram. Worldwide data is expected to hit 175 zettabytes by 2025…
Handling “Json” and “Unstructured” Data in SQL
The below is to understand how we can handle JSON data in prior versions of SQL Server 2016. Sample JSON data is: {“accountNumber”: 2020112, “pin”: 2525}, {“accountNumber”: 2567899, “pin”: 1462} {“accountNumber”: 6789925, “pin”: 2614} {“accountNumber”: 9925678, “pin”: 6142} This can be extracted into columns easily in SQL Server 2016 with the help of following…
Analytical & Window Functions
Please refer to my previous post in which schema and data for EMP and DEPT tables available. In this article we are about to discuss about SQL Server Analytical and Window functions. As stated in Microsoft docs, analytic functions calculate an aggregate value based on a group of rows. Unlike aggregate functions, however, analytic functions…
SQL Server – Date Time Functions
Here are a few examples of functions that can help you manipulate DATE in different manners. These will comprise most of your daily job routines. The below set of Date/Time functions are used consistently by SQL Users as they are most oftenly required to retrieve specific data from the database. ——————————————————————————— Please note that this…
SQL Server – Monthly Report – Group by Month
The following example guide you to retrieve a monthly report. As discussed earlier, If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Here is an example to generate a monthly report by GROUPING DATE column. CREATE TABLE Test1( AccountID INT,…
SQL Server – Daily Report – Group by Date
Like hourly report, the following example guide you to retrieve a daily report. As discussed earlier, If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Here is an example to generate a daily report by GROUPING DATE column. CREATE TABLE…
SQL Server – Hourly Report – Group by Hour
Often it is important to know how many inserts occur per hour in a transaction table OR how many payments are made per hour OR to retrieve the hourly report, whatever term it is, the necessity is to produce a report from a datetime column based on hours. If the date column is SMALLDATETIME or…
SQL Server – CHARINDEX – Oracle INSTR
There is a ‘CharIndex’ function in SQL Server which is similar to the Oracle ‘Instr’ function. In Oracle, the syntax of the INSTR function is :instr( string1, string2 [, start_position [, nth_appearance ] ] ) ‘String1’ in the above syntax represents the string that you need to search in, whereas String2 is the substring that…
SQL Server – GETDATE(), CURRENT_TIMESTAMP in DEFAULT statements
Most of the transactional tables generally do require to have a column with DEFAULT value as current date time to identify when the transaction had took place. To accomplish this, in CREATE TABLE statement you can use GETDATE() function or CURRENT_TIMESTAMP (ANSI SQL equivalent to GETDATE function) as DEFAULT. However there is no difference in…
Big Data and SQL 