Forem: Manvendra Deo Singh

How to use SQL Server T-SQL function CHARINDEX in SQL queries

Manvendra Deo Singh — Wed, 18 May 2022 15:25:43 +0000

This article will explain the SQL Server string function CHARINDEX and its use cases. This function returns the first position of a specified string in the first argument from another input string specified in the second argument.
The syntax of this function is given in the below SQL code.

-- Syntax
CHARINDEX (expressionTOfind, expressionTOsearch, starting_position)

Here,
expressionTOfind is a character expression or substring that needs to be searched from another string specified in the expressionTOsearch argument

expressionTOsearch is a character expression in which the first argument specified under expressionTOfind will be searched

starting_position is an optional argument. If this argument is specified, then the search will start after this value position.

If we specify a NULL value for any of the argument expressionTOfind or expressionTOsearch then the CHARINDEX function will also return its output as NULL and it will return 0 if expressionTOfind will not be there in expressionTOsearch.

Use cases of T-SQL function CHARINDEX

I have given 5 use cases of this function in the below section. You can use SQL Server management studio or dbForge SQL manager to practice these use cases or for your other development activities.

Let’s start with the first use case in which I have searched a string from a specified input string.

Simple use case of CHARINDEX with the input string

There are two statements in the below example. The first statement is used to search the starting position of expression “Manvendra” which is the expressionTOfind argument from another input string “Name – Manvendra Deo Singh. Manvendra lives in Gurgaon” which is the expressionTOsearch argument.

I have not specified starting_position argument in the first statement whereas I have specified this argument in the second statement as 10 keeping the rest of all arguments as it is.

By default, CHARINDEX will start searching the pattern from starting position 1 of any string if you will not specify the starting_position argument in this function. We can validate it in the below result.

-- Return starting position of Manvendra in a string
SELECT CHARINDEX(‘Manvendra’, ‘Name – Manvendra Deo Singh. Manvendra lives in Gurgaon’),
    CHARINDEX(‘Manvendra’, ‘Name – Manvendra Deo Singh. Manvendra lives in Gurgaon’,10)

The first statement searches the specified substring “Manvendra” from the given input string from starting position 1 as we have not mentioned any start position whereas the second statement will search the same pattern from starting position 10 as I have specified the starting_position argument value as 10 and that is why there is different output for same pattern search given in the above use case.

The starting position of substring “Manvendra” is returning as 8 in the first statement whereas it is returning as 29 if we specify the starting_position argument to 10 in this example.

The starting_position argument allows this function to skip all characters before the specified value from the search operation and start searching from the value specified under this argument.

I will take another example in which I will show you the result if we pass the NULL value to its arguments.

As I have already mentioned above If we specify a NULL value for any of the value expressionTOfind or expressionTOsearch then the CHARINDEX function will also return its output as NULL and it will return 0 if expressionTOfind will not be there in expressionTOsearch.

Let’s verify the above statement by executing the below T-SQL statements. Here, I have specified NULL value as expressionTOfind in the first statement whereas “SQL” in the second statement along with keeping the input string as the same in which searching will be done.

-- Use CHARINDEX with NULL
SELECT [NULL Position] = CHARINDEX(NULL, ‘Name – Manvendra Deo Singh. Manvendra lives in Gurgaon’),
    [SQL Position] = CHARINDEX(‘SQL’, ‘Name – Manvendra Deo Singh. Manvendra lives in Gurgaon’),

Here is the output. We can see if we pass NULL value then CHARINDEX function is returning as NULL whereas if we search the starting position of a string which is not there in the second argument then its output is returning as 0.

We can revalidate by looking at the input string and find that the “SQL” string is not there in the second argument.

The SQL function CHARINDEX will also return the output if we use wildcards to search the characters as I have searched in the below example.

SELECT CHARINDEX(‘-‘, ‘Name – Manvendra Deo Singh. Manvendra lives in Gurgaon’),
CHARINDEX(@, ‘Email – manuxxx@gmail.com’)

You can see the starting position of both characters in the output section.

Use CHARINDEX with variable

This section will show how to use SQL function CHARINDEX with variables.

Here, I have declared a variable @input_string and assigned a string to this variable.

Next, I used this function CHARINDEX to search starting position of various characters by specifying the @input_string variable and not the full string.

Have a look at the T-SQL statements.

DECLARE @input_string varchar(50)
SET @input_string = ‘Name – Manvendra Deo Singh. Manvendra lives in Gurgaon. Email – manuxxx@gmail.com’
SELECT [Gurgaon Position] = CHARINDEX(‘Gurgaon‘, @input_string),
    [@ Position] = CHARINDEX(‘@’, @input_string),
    [SQL Position] = CHARINDEX(‘SQL’, @input_string)

I have searched starting position of 3 different character\strings “Gurgaon”, “@” and “SQL”. We can the result set for each character in the below screenshot.

I have taken another example in which I have also specified 3rd argument starting_position from where the search will start to find the specified expressionTofind expression from variable @input_string.

I have used the same expressionTofind expression as “Man” and specified different values for the 3rd argument starting_postion.

We will get the below output post executing the above SQL statements. We can see the output is returning different values for the same substring search because of specifying the 3rd argument starting_position.

Case sensitive search using CHARINDEX

We can also search a substring using a specific collation by defining COLLATE statement with the desired collation name.

Have a look at the below example in which I have specified 2 different collations using COLLATE statement in function CHARINDEX and searched the same substring “Gurgaon” with case sensitive and insensitive options with their respective collations.

Here, the first statement is searching “gurgaon” keeping the case insensitive as I have specified collation as Lation1_General_CI_AS whereas the rest of the two searches “GURGAON” and “gurgaon” are searched with case sensitive collation as shown in the below example.

DECLARE @input_string varchar(50)
SET @input_string = ‘Name – Manvendra Deo Singh. Manvendra lives in GURGAON. Email – manuxxx@gmail.com’
SELECT [gurgaon Position with CI] = CHARINDEX(‘gurgaon‘, @input_string COLLATE Lation1_General_CI_AS),
 [GURGAON Position with CS] = CHARINDEX(‘GURGAON‘, @input_string COLLATE Lation1_General_CS_AS),
[gurgaon Position with CS] = CHARINDEX(‘gurgaon‘, @input_string COLLATE Lation1_General_CS_AS)

When we go with case sensitive search then the exact text or string should match. You can see the input string has string “GURGAON” so a search where “GURGAON” is specified has returned the result whereas another search “gurgaon” has returned as 0 whereas when we mention case insensitive collation then lower-case substring “gurgaon” has also returned the correct result.

Use CHARINDEX with the table column

We can also use the SQL function CHARINDEX with column names. Here, I will show you an example in which I will use system table sys.databases and returned the list of all databases and the starting position of a character given in their names.

SELECT name, create_date, CHARINDEX(‘DB‘, name) AS [Position of DB]
FROM sys.databases
Go

Here, I have searched the character “DB” in the column name of the system table sys.databases. It will return the list of all databases and the starting position of the characters DB if it is there in the database names. If string DB will not be found in the database name then the output will return as zero.

Use CHARINDEX in the WHERE clause

This use case will demonstrate using the SQL function CHARINDEX in the WHERE clause to define a condition. The below example will return the list of all databases whose name has character DB at more than 4th position.

SELECT name, create_date 
FROM sys.databases
WHERE CHARINDEX(‘DB‘, name)>4

You can validate the below output in which we can see all databases which have the “DB” character in their names after the 4th position.

Conclusion

This article will help you understand one of the SQL Server string functions CHARINDEX. You can go ahead and test the above use cases to learn this function and use it in your business requirements. Please share this article and comment with us your feedback in the comment section so that we can improve in a better way.

Understanding T-SQL operator CROSS APPLY, and OUTER APPLY in SQL Server

Manvendra Deo Singh — Tue, 17 May 2022 12:06:33 +0000

SQL Server offers JOIN statements to join more than one tables in the T-SQL statements and get the desired output based on our business needs. We all know that we get distinct requirements in our day to day business operations so one solution does not work for every requirement.

Let’s take an example, suppose you got a requirement to join a SQL Server table and a table valued function in a T-SQL query to get your desired output. T-SQL statement JOIN will not work in this case and that is why Microsoft has introduced APPLY operators in T-SQL query to address this requirement. You can use either SQL Server management studio or other third party SQL tools for your database developments.

We can get output using the APPLY operator by joining more than one table just like JOINs as well as by joining tables and table valued functions together.

There are 2 types of APPLY operators.

CROSS APPLY
OUTER APPLY

The result set of the CROSS APPLY operator is similar to the INNER JOIN statement if you join two tables whereas OUTER APPLY works like LEFT OUTER JOIN if you compare their result set.

I will show you the comparison of CROSS APPLY & INNER JOIN and OUTER APPLY & LEFT OUTER JOIN in the below use cases to demonstrate their similar result set.

The CROSS APPLY operator returns a result set from the left table expression if it matches with the right table expression whereas the OUTER APPLY returns the result set from the left table expression irrespective of its match with the right table expression.

Today, I will demonstrate the use cases of both APPLY operators by joining more than one table and by joining tables with the table valued functions in this article. I will also show you the error you will receive when you join a table with a table valued function using a JOIN statement.

There are two sets of use cases in this article. One is by joining a table with a table valued function using APPLY operator and another one is by joining two tables using APPLY operator in the below section of this article.

Join table and table valued function using APPLY operator

Let’s start the first set of use cases in which we will join the SQL Server table and its table valued function using APPLY operator to get our desired output.
I already have 2 tables in my test database TESTDB. The names of the tables are Associates and OrderDetails. The Associates table stores information about each employee whereas the OrderDetails table stores sales related information.
Now, I will create a table valued function named fn_returnempsales to return sales details based on the employee id. Next, we need to create a table valued function and then we will join them to get the result set.

Create a table valued function

I have used the below statements to create a function named fn_returnempsales to return all the sales details based on the employee id. We need to pass the employee id to return all products sold by that employee.

-- Create a function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION fn_returnempsales(@PersonID AS INT)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM [dbo].[OrderDetails] od
WHERE od.Person.ID = @PersonID
)
GO

The above T-SQL statement has been successfully completed in the below image.

Let’s validate this function by passing any employee id to see all product sales done by that employee along with the other details as shown in the below example. We can see employee if 5 is Julie and she has converted 3 sales as shown in the below image.

Test joining table and table valued function using INNER JOIN

Here, we can see what will happen if we join a table with a table valued function using INNER JOIN. The query is throwing an error using INNER JOIN as we can see in the below image hence the requirement came to introduce APPLY operator in T-SQL.

Next, I will show you the joining table with table valued function using APPLY operators in the below section. Let’s start joining using CROSS APPLY first.

Join table and table valued function using CROSS APPLY

Here, I will join table Associates with table valued function fn_returnempsales to return its result set. I am fetching 3 columns from table Associates and 3 columns from table valued function fn_returnempsales.

SELECT a.name, a.city, a.designation, fn.ProductName, fn.Price, fn.InvoiceMonth
FROM [dbo].[Associates] as a
CROSS APPLY [dbo].[fn_returnempsales](a.ID) as fn
GO

When you run the above T-SQL statement, you will get the below result set. We can see all matched rows are displayed in the below output.

Join table and table valued function using OUTER APPLY

Here, I will show you the same example which I showed you in the above section but using OUTER APPLY.

SELECT a.name, a.city, a.designation, fn.ProductName, fn.Price, fn.InvoiceMonth
FROM [dbo].[Associates] as a
OUTER APPLY [dbo].[fn_returnempsales](a.ID) as fn
GO

The below result set will be displayed if you run the above query. We can see the number of rows is showing more than the no of rows displayed in the result set of the CROSS APPLY operator.

If you notice the below result set, you will observe that it is displaying even those rows which have NULL or no values. It means employees given from row no 29 have not made any sales and that is why their entries are not there in the OrderDetails table and showing their values as NULL.

If you want to filter the result set by applying additional conditions with WHERE clauses, then you can also do it with APPLY operator.


--Use APPLY operator with WHERE clause.
SELECT a.name, a.city, a.designation, fn.ProductName, fn.Price, fn.InvoiceMonth
FROM [dbo].[Associates] as a
CROSS APPLY [dbo].[fn_returnempsales](a.ID) as fn
WHERE fn.Price>2000
GO
SELECT a.name, a.city, a.designation, fn.ProductName, fn.Price, fn.InvoiceMonth
FROM [dbo].[Associates] as a
OUTER APPLY [dbo].[fn_returnempsales](a.ID) as fn
WHERE fn.Price>2000
GO

Here, I fetched details of products and employees who have generated invoices of more than $2000.

Join tables using APPLY operator

We have learned how to use APPLY operator to join a table and a table valued function to get the desired result set. Now, I will demonstrate how to join more than one table using APPLY operator. I will also compare the result set returned by joining two tables using APPLY operator and using JOIN statements.

Join tables using CROSS APPLY

You can see the below T-SQL statement in which I have joined two tables Associates and OrderDetails using the CROSS APPLY operator.

SELECT a.name, a.city, a.designation, od2.ProductName, od2.Price, od2.InvoiceMonth
FROM [dbo].[Associates] as a
CROSS APPLY (
SELECT *
FROM [dbo].[OrderDetails] as od1
WHERE a.ID=od1.PersonID
) as od2

The result set of the above query is shown in the below image.

As I have stated above that the result set of CROSS APPLY will return equivalent to the INNER JOIN so let’s validate this statement with the help of the below example where I will execute two sets of queries; one is using INNER JOIN and another one is using CROSS APPLY.

-- Output using INNER JOIN
USE TESTDB
Go
SELECT a.name, a.city, a.designation, od.ProductName, od.Price, od.InvoiceMonth
FROM [dbo].[Associates] as a
INNER JOIN [dbo].[OrderDetails] as od
ON a.ID=od.PersonID
WHERE od.Price>2000

--Output using CROSS APPLY
USE TESTDB
Go
SELECT a.name, a.city, a.designation, od2.ProductName, od2.Price, od2.InvoiceMonth
FROM [dbo].[Associates] as a
CROSS APPLY (
SELECT *
FROM [dbo].[OrderDetails] as od1
WHERE a.ID=od1.PersonID) as od2
WHERE od2.Price>2000

Here is the output of the above statements. I have applied a condition to filter the output and reduce the number of rows returned for both outputs. Here we can see the output of both queries is the same. If you will remove the WHERE clause statement to remove the price filter, then also you will get the same output from both the queries. The number of rows will be increased in both results but they will be the same in both outputs.

Join tables using OUTER APPLY

Let’s understand the output of joining two tables using OUTER APPLY. I have used the same T-SQL query from the above example and just changed the APPLY operator from CROSS APPLY to OUTER APPLY.

SELECT a.name, a.city, a.designation, od2.ProductName, od2.Price, od2.InvoiceMonth
FROM [dbo].[Associates] as a
OUTER APPLY (
SELECT *
FROM [dbo].[OrderDetails] as od1
WHERE a.ID=od1.PersonID) as od2

When you execute the above query, you will get a result set like the one given in the below image. Here, we can see the number of rows has been increased if you compare the above example.

Here, we are also getting results for which there are NULL values. Those employees who have not done any sales so their entries are not they’re in the OrderDetails table but still, we can see them in the result set with the NULL value in the products and price columns because the result is showing all entries from the table Associates.

The OUTER APPLY returns a similar result to the LEFT OUTER JOIN. We can get it validated by joining both tables using the LEFT OUTER JOIN statement. Have a look at the below T-SQL where I have joined both tables using LEFT OUTER JOIN.

USE TESTDB
Go
SELECT a.name, a.city, a.designation, od.ProductName, od.Price, od.InvoiceMonth
FROM [dbo].[Associates] as a
LEFT OUTER JOIN [dbo].[OrderDetails] as od
ON a.ID=od1.PersonID

Here, you can compare the output of this query with the last query shown above. We can see both result sets are the same.

Conclusion

Today, I have explained APPLY operator and its use cases by joining tables and table valued functions in this article. I also explained CROSS APPLY and OUTER APPLY and their result set comparison with the INNER JOIN and LEFT OUTER JOIN statements. Please share this article and comment with us your feedback in the comment section so that we can improve in a better way.

How to create a View in SQL Server

Manvendra Deo Singh — Tue, 17 May 2022 11:39:38 +0000

Today I am going to explain how to create a view in SQL Server. Let’s first understand what SQL Server view is. The meaning of View in English is the ability to see something or to inspect something. Now, we will correlate this in SQL Server.

We know Tables in SQL Server, Tables are used to store data in the forms of rows and columns in relational database systems like SQL Server, Oracle, or other RDBMS systems. SQL Server VIEW is a database object which offers us the ability to see or inspect what is there in their respective Tables on which VIEW has been created without touching or accessing the actual Table in which data is stored.

Views do not store data physically in the database and are fetched directly from their respective tables whenever you try to access that View and that’s why we term it as a virtual or logical table.

When you access a View, it executes its SQL query and returns the output as per the data selection in the query. You should also note that when you create an index on the View, data got physically stored in the database for that View.

Next, you might be thinking about what the use of SQL Server VIEW is if we already have Tables in the database. The reason for creating a view in SQL Server is to address below concerns of the database users.

Users can look at or customize data from different perceptions based on their needs. They don’t need to make any changes in actual tables, rather they can get filtered columns from multiple tables in a single VIEW.
VIEWs are useful if you don’t want to allow users to access the database tables. You can create a VIEW based on the user's need and ask them to use that View as a table for their business need. It also improves security and secure table data.
VIEWs are also useful in a backward compatible interface to a table that used to exist, but its schema has changed over time.

There are several types of VIEWs in SQL Server.

User-defined view
System view
Indexed view
Partitioned view

User-defined View is a standard view that a user can create either using query or using GUI through SQL Server Management studio.

System View is an inbuilt system created catalog view using which we fetch various details of SQL Server instances like sys.databases, sys.database_files, etc.

Next is Indexed View, if you create an index on a view then it means the view definition will be computed, and the resulting data will be stored in the database, unlike standard view. Indexed views are created by creating a clustered index on a view. It also improves database performance and is best suited for queries that aggregate more rows.

The last one is Partitioned View which is used to join horizontally partitioned data from a set of member tables across one or more servers.

Now, we will learn how to create a VIEW in SQL Server. We can create a view using T-SQL as well as using GUI through SQL Server Management Studio. Let’s start with the T-SQL statement.

-- Create View Syntax
CREATE VIEW ViewName AS
SELECT ColumnName1, ColumnName2, ColumnName3,..
FROM TableName
WHERE condition;

I have a test database named TESTDB as shown in the below image. You can see the list of all views in the Views folder of the database. You need to connect to your SQL Server instance and then expand the Databases folder. Expand your target database in which you want to create a view or you want to see a list of views of a database.
I have expanded the Views folder of database TESTDB and we can see there is no user-defined view in this database. You can get a list of all system views by expanding the System Views folder shown under the Views folder of the database TESTDB.

Create SQL Server View using T-SQL

We will use CREATE View T-SQL statement to create a view in SQL Server. The syntax of view creation is already given in the above section.

Now, I want to create a view named ProductSalesVW in the TESTDB database where I will include 3 columns PersonName, Price, and InvoiceMonth from table OrderDetails to display the list of product sales each month.

-- Create a View to display month wise product sales
CREATE VIEW ProductSalesVW AS
SELECT ProductName, Price, InvoiceMonth
FROM OrderDetails

We can execute the above T-SQL statement to create the view. The above statement was successfully executed as shown in the below image.

You can also verify whether a view has been created in your database or not by expanding the Views folder of the database TESTDB as shown in the below image. You can use system object sys.views to get a similar list. I will show you later how to use this system object to get the list of all views.

Now, you can fetch data from the newly created View. You just need to simply run the SELECT statement the way you run on any table to fetch its data.

Here, I have executed a simple SELECT statement to return all data from this view and we can only see 3 columns that we have chosen to create this view.

If you want to apply specific conditions to filter the output then you can also do it by using the WHERE clause in the CREATE VIEW statement.

Next, let’s create a new view named HighValueProductSalesVW which will display a list of all products for which invoice cost is more than $1000.

-- Create View to display month wise product sales where the invoice is more than $1000
CREATE VIEW HighValueProductSalesVW AS
SELECT ProductName, Price, InvoiceMonth
FROM OrderDetails 
WHERE Price >=1000

Once you will execute the above statements, a new view will be created. Let’s fetch the data using this newly created view. The below image is showing the output of the newly created view and we can see only products whose price is more than $1000.

Next, let’s create a view named EmpProductSalesVW using which we will fetch data from more than one table. We will take an extension of the above example where we will add additional table Associates to fetch employees’ details who have sold these high-value products.

-- Create View to display month wise product sales where the invoice is more than $1000
CREATE VIEW EmpProductSalesVW AS
SELECT o.ProductName, o.Price, o.InvoiceMonth, a.Name, a.Designation
FROM OrderDetails o INNER JOIN Associates a
ON o.PersonID=a.ID
WHERE Price >=1000

The above view has been successfully created in the below image.

Next, we will run this newly created view to fetch its output and we can see employee names and their designation is showing in its output.

Till now, we learned how to create SQL Server views with different requirements in the above steps. Next, we will learn how to validate or check these newly created views using the T-SQL statement. If you want to display a list of all user views then you can simply run the below T-SQL statement to get this list. I have executed the below statement and got the list of views that we have just created in this article. I don’t had any views created in this database earlier.

-- Get a list of all Views
SELECT name, type, type_desc, create_date
FROM sys.views

Have a look at the output of the above query. It is showing all 3 views which we created in the above steps.

You can also use another system view sys.all_views to display the list of all views created for your target database including all system views as well.

Create SQL Server View using GUI through SQL Server Management Studio

This section will demonstrate step by step process to create a SQL Server View using GUI through SQL Server management studio. There are various tools or add-ins also available in market for database development like dbforge SQL autocomplete which you can use for this activity.

First, you need to connect to your SQL Server instance in SQL Server management studio on which your target database is hosted under which you will create a view. Expand the Databases folder in the management studio and finally target the database in which we will create the View. I will create another view in my test database TESTDB.
Right click at the Views folder and click on the New View… option as shown in the below image.

You will get the below window to add tables from which you want to select columns that will be included in the view. I have selected 2 tables as highlighted in the below image and then clicked on the Add button. I will get the product name and POC (point of contact) from one table OrderDetails and details of each POC from another table named Associates.

Once you will click on the Add button post selecting all your desired tables from which columns will be selected in the View, another popup window will be displayed for each table containing their column names as shown in the below image. This section is known as the Diagram pane. Now click on the Close button from Add Table window to close that window.

The relation between tables will be displayed as per their relationships or you can also join both tables based on their relation key. You can see the link between both tables on column Associates.ID and OrderDetails.PersonID.

The next step is to select columns that will be part of View. Click at the check box to select or uncheck the check box to deselect the columns. You can also see the T-SQL statement based on your selection in the query pane.

The next option is to choose criteria for your view and that is why we call it the Criteria pane. Here, you can choose sort type, sort order, filter output based on a specific condition, or add an alias for your column names. You will notice a T-SQL statement showing just below the criteria pane will keep on changing based on your selection or deselection criteria.

Once you have designed your View then you can execute it to display the result in the same window. Right click anywhere on the screen and click on the Execute SQL option as given in the below image.

You will get its output in the output section as shown in the below image. Here you can understand the data whether it is adequate and as per your need or not. You can make changes based on your needs to change the data perspective. We can see all data returned from this View in the below screen and it is absolute as per our expectation.

Next, we will save this View. Click on the Save button, you will get the below screen to name this view. Enter the appropriate name under which you want to save this view.

You can validate it by either expanding the Views folder or by accessing the sys.views system object to ensure this view has been created or not. New View is shown in the below image during our validation.

If you want to access the data from this newly created view, you can run it SELECT statement as I have executed in the below image to display its data.

Conclusion

Today, I have explained the basic understanding of SQL Server View and its step-by-step process to create it, check it or access it to get its data. Please share this article and comment with us your feedback in the comment section so that we can improve in a better way.