SQL Server TRY PARSE() Function

SQL Server is a Relational Database Management System(RDBMS), which is used to handle, manage and utilize the data of organizations and so on. It provides various effective functions to manage things efficiently and gives exceptional output. In this article, we will understand one of the important functions which is the TRY PARSE function in detail. After reading this article you will have in-depth knowledge about TRY PARSE Function.

TRY PARSE Function

When we deal with databases, we have different data types. In SQL Server, we have various data types to store different types of data. Like int data type for integers, varchar data type for strings, date data type for storing the data, and XML for XML type data.

For such types of data conversions, we have several functions provided by the SQL server to achieve this. We shall understand such a function which is the TRY_PARSE() function with the help of a suitable example.

The TRY_PARSE() Function is a type of function that is used to convert string type of data into numeric and date type of data. This type of function is very useful when we have data from users or some kind of external data where datatypes may vary. So this function handles the data very effectively and prevents errors.

For our example, we need the SQL Server 2012 installed along with the SQL Server Management Studio workbench.

Syntax:

TRY_PARSE(data AS dataType)

Explanation: Here data is the given column(student_dob) to be converted while the dataType specifies the required format into which the data must be converted to.

Prerequisites 

1. SQL Server 2012

2. SQL Server Management Studio.

Table Creation

Let us consider we are building a database to keep track of each student’s data in a college. This student’s data includes the student’s name, student’s city,and student’s date of birth along with a unique student id that would be associated with each student enrolled in the college.

The purpose of having this student’s database is to store each student data in the database so that we can accordingly utilize the data stored in this database for achieving certain functionalities. This would be seen later in this example.

Let us create a database named geeksforgeeksdb.
Query:

CREATE DATABASE geeksforgeeksdb;
USE  geeksforgeeksdb;

Explanation: As seen from the above query, we use the create command to create this database. In this database, we will create a table called the students table to store the student’s information.

Creating a Table

Query:

Let’s create a table called students for understand the function more precisely.

CREATE TABLE students
(student_id VARCHAR(32),
student_name VARCHAR(32),
student_city VARCHAR(32),
student_dob VARCHAR(32)
PRIMARY KEY(student_id));

Let’s insert some data into students table to performing some operations.

Query:

INSERT INTO students(student_id,student_name,student_city,student_dob)
VALUES('10', 'robin', 'mumbai', '02-08-1994');
INSERT INTO students(student_id,student_name,student_city,student_dob)
VALUES('20', 'jack', 'delhi', '04-07-1992');
INSERT INTO students(student_id,student_name,student_city,student_dob)
VALUES('30', 'jane', 'chennai', '04-10-1995');
INSERT INTO students(student_id,student_name,student_city,student_dob)
VALUES('40', 'john', 'mumbai', '07-08-1999');
INSERT INTO students(student_id,student_name,student_city,student_dob)
VALUES('50', 'julie', 'mumbai', '01-06-1991');

Output:

Students Table with Data

Explanation: As seen from the above query, we have inserted 5 sample data into the students table consist of the student’s name, student’s city, student’s id, and date of birth.

Example 1: Converting a VARCHAR Type to DATE Type

Query:

SELECT TRY_PARSE(student_dob AS DATE) as student_dob_in_date
FROM students;

The student_dob column successfully converted into the date data type

Student DOBs in date data type

Explanation: The TRY_PARSE() function takes the given input student_dob and then converts this input into the required format. Here, the input is being converted into the date data type format.

Example 2: Converting a VARCHAR Type to INT Type

The student_id is currently of the varchar data type. In our output, this student_id column data should be in the correct format which is in the number (integer) type format. This can be done using the TRY_PARSE() function.

Query:

SELECT TRY_PARSE(student_id AS INT) as student_id_in_int
FROM students;

Output:

Students Id in int data type

Explanation: As seen from the above query, we have successfully converted the student_id column from one data type to another data type i.e, varchar data type to the int data type. This was possible with the TRY_PARSE() function.

Example 3: Converting a VARCHAR Type to an INT Type using CASE Expression

Consider a use case where we have a scholarship being awarded to the students. As an example, let us consider that this scholarship can be taken only to those students who got themselves enrolled in the university at the earliest.

That is, only the top 3 students. These top 3 students are those students whose student_id value is lesser or equal to 30. Those students with their student_id value greater than 30, are the ones who were enrolled in the university later and hence they would not be eligible for scholarship.

We require a list of the students records. Each record must contain the students name if the student is eligible for the scholarship or else Not Selected should be displayed for those records where the student was ineligible for the scholarship, along with the student’s city.

This is our given requirement where we need our output to consist of the 2 columns namely.

• selected_students: Displaying the students name for all eligible students, while displaying “Not Selected” for ineligible students.

• student_city: This column displays the student’s city from where he/she belongs.

Query:

SELECT CASE WHEN TRY_PARSE(student_id AS INT) <= 30
THEN student_name
WHEN TRY_PARSE(student_id AS INT ) > 30
THEN 'Not Selected'
ELSE
'Not Selected'
END AS selected_students,
student_city
FROM students;

Output:

The selected_students column along with the student’s city that has been selected for the scholarship is displayed.

Selected students list for scholarship

Explanation: As seen from the above query, Here we have used the CASE, WHEN and THEN clauses in our query. We will write 2 cases which are:

• 1st case: Condition for scholarship eligibility which is student_id column <= 30.
• 2nd case: Condition for ineligibility for scholarship, which is student_id column > 30

We need to check these conditions with the value 30. However, to do the following, first, we need to convert the student_id column into the appropriate data type for comparing with the given value (30).

So to convert the student_id column into the int data type we use the TRY_PARSE() function providing the student_id column as the input.

We also provide a third output” Not Selected“ as a default output to be displayed in case none of the 2 conditions are satisfied using the ELSE clause as seen in the query.

Working of the Above Query:

This query then scans each row of the students table. It first checks, after converting the student_id column into the int data type, if the 1st condition is satisfied in the When clause.

If not satisfied, it moves to the 2nd condition specified in the When clause and checks if it is being satisfied.

If no condition is satisfied, it moves to the default output to be displayed as mentioned in the Else part.

If any of the conditions are satisfied it will display the corresponding output provided in the Then clause of the query for that row.

In this way, each row of the students table is scanned to check for the matching conditions using the CASE, WHEN and THEN clause along with the TRY_PARSE() function of the SQL Server.

As we see from the output, it consists of the selected_students column list that contains the corresponding names of all the eligible students for the scholarship, along with their city names.

Difference Between TRY_PARSE() vs PARSE() Function

SQL Server function	TRY_PARSE()	PARSE()
Query	SELECT TRY_PARSE(student_id AS INT) FROM students;	SELECT PARSE(student_dob AS INT) FROM students;
Conversion Process	Incompatible data types result in NULL values as output	Incompatible data types result in Failed Conversions
Output	No error message is displayed for failed conversions	The Error message is displayed stating failed to convert the given data types
Data State	When using the TRY_PARSE() function, the input data stored need not be of a compatible format	When using the PARSE() function, input data must be of a compatible format or else it will result in failed conversions.
Use Case	Not suitable to be used when arithmetic operations are to be performed over the data stored in database, due to substitution of NULL values in case of failed data conversions	Best suited to be used when certain arithmetic operations are required to be performed over the data to protect our data from a dirty state, due to the absence of NULL values for failed conversions

Conclusion

From our example, we saw how we could utilize the SQL server’s TRY_PARSE() function along with a combination of certain other functions to fulfill a certain requirement. This is how we use the TRY_PARSE() function for the conversion of data types into different data types. We then accordingly compare them with one another to filter out those specific records from the database tables, in the form of results displayed as the output.