How to convert String to Integer SQL and Database? MySQL, Oracle, SQL server and PostgreSQL Example

 Hello guys, if you are wondering how to convert VARCHAR to String in SQL then you are at the right place. In this article, we will cover various techniques to convert String to Integer in all databases - MySQL, Oracle, SQL server and PostgreSQL. To perform tasks or correlations or change between information in a SQL Server data set, the SQL information kinds of those values should coordinate. At the point when the SQL information types are unique, they will go through a cycle called type-projecting. The transformation of SQL information types, in this cycle, can be implied or unequivocal.

The information type change works usually utilized uncommonly to fulfill the information guidelines of the objective items or frameworks. We are on the planet managing heterogeneous information.

Some of the time the information kinds of the information need to get switched over completely to different information types for computations or change or cycles or to meet objective information designs. For instance, when we increase decimal sort information with a whole number, the information goes through interior change and certainly switches a number over completely to decimal information type and the outcome is of decimal information.

Implicit and Explicit SQL conversion

At the point when we manage two qualities which are same in nature however various information types, in the background, the data set motor proselyte the lower information type values to higher information type before it could proceed the estimation. This type is known as a certain transformation. Then again, we have unequivocal transformations where you either call a SQL CAST or SQL CONVERT capability to change the information type.

SQL CAST v/s CONVERT

• CAST is simply an ANSI-SQL Standard. However, CONVERT is SQL Server explicit capability in like manner we've to_char or to_date in Oracle
• CAST is dominatingly accessible in all data set items as a result of its transportability and ease of use
• There won't be a significant contrast as far as inquiry execution between SQL Cast and SQL Convert capabilities. You can see a slight contrast in execution times this is a result of inside change of SQL CAST to its local SQL CONVERT capability yet CONVERT capability accompanies a choice "Style-code" to determine different blends of date-and-time, decimals, and financial qualities. Anyway, SQL CONVERT capability runs somewhat better compared to the SQL CAST capability

1. MySQL/ SQL Server

To cast VARCHAR to INT in MySQL, we can use in-built CAST() function. Syntax for the same is given below:

cast(Value as DataType)

Step by step explanation:

Step 1 : Create a table

mysql> create table DemoTable -> ( -> Value varchar(100) -> ); 

Query OK, 0 rows affected (0.51 sec)

Step 2 : Insert values into table

mysql> insert into DemoTable values('111');
Query OK, 1 row affected (0.26 sec) 

mysql> insert into DemoTable values('222'); 

Query OK, 1 row affected (0.16 sec)

Step 3 : Display all records of table

mysql> select * from DemoTable;

Output:

+-------+ | Value | +-------+ 

| 111 | 

| 222 | 

+-------+ 2 rows in set (0.00 sec)

Step 4 : Using CAST() to convert VARCHAR to INT

Syntax:

SELECT CAST(ColumnName AS DataType) FROM TableName;

Our code:

mysql> SELECT CAST(Value AS UNSIGNED) FROM DemoTable;

Output:

+-------------------------+ 

| CAST(Value AS UNSIGNED) | 

+-------------------------+ 

| 111 | 

| 222 | 

+-------------------------+ 

2 rows in set (0.00 sec)

Hence, we have changed datatype from VARCHAR to INT

2. Oracle/PLSQL

We will use TO_NUMBER function to convert string to number in Oracle.

Syntax:

TO_NUMBER( stringToConvert [, format_mask] [, nls_language] )

Parameters:

• stringToConvert : The string that will be switched over completely to a number.
  
  
• format_mask (Optional) : This is the format that will be utilized to change over the given string to a number.
  
  
• nls_language (Optional) : This is the nls language used to change over the given string to a number.

Example:

TO_NUMBER('1210.73', '9999.99') 

Result: 1210.73 

TO_NUMBER('546', '999') 

Result: 546 

TO_NUMBER('23', '99') 

Result: 23

Since the format_mask and nls_language boundaries are discretionary, you can just change over a text string to a numeric worth as follows:

TO_NUMBER('1210.73') Result: 1210.73

Hence, we discussed how to convert string to number in Oracle

3. PostgreSQL

Solution 1:

We'll utilize the :: administrator.

 

Here is the query you'd compose:

SELECT ' 5800.79 '::DECIMAL;

Here is the outcome:

numeric
5800.79

As you notice, the main and it were taken out to trail spaces.

Explanation:

Utilize the :: administrator to change strings containing numeric qualities over completely to the DECIMAL information type. In our model, we changed over the string ' 5800.79 ' to 5800.79 (a DECIMAL worth).

This administrator is utilized to change over between various information types. It's exceptionally well known inside PostgreSQL. You can likewise utilize the standard SQL administrator, CAST(), rather than the :: administrator.

Solution 2:

Query:

SELECT CAST(' 5800.79 ' AS DECIMAL );

Here is the outcome:

numeric
5800.79

Note: that CAST(), like the :: administrator, eliminates extra spaces toward the start and end of the string prior to changing it over completely to a number.

The PostgreSQL information base gives another method for changing over. Utilize the TO_NUMBER() capability assuming that you really want to change over additional confounded strings. This capability takes two contentions: the string to change over and the organization veil that demonstrates how each person in the string ought to be deciphered. See the model beneath:

Solution 3:

SELECT TO_NUMBER(' 5 800,79-', 'FM9G999D99S' );

Here is the outcome:

numeric
5800.79

The arrangement string depicts the string containing the number (input esteem as string). In this model, this veil contains the image 'FM', which eliminates driving and following spaces. The '9' demonstrates one digit (in our model, 5) and 'G' addresses a gathering of digits (in our model, one space shows a gathering of thousands).

Then, '999' demonstrates three additional digits (800).

The 'D' image indicates a decimal marker (here, a point/speck '.'). After the decimal image comes '99', or two partial digits.

The last image, 'S', indicates the utilization of an or more or less sign (our number is negative, so it gets a less).

Here are the most involved codes for this mapping:

That's all about how to convert String to Integer in Database and SQL. In the above article, we discussed how to convert datatype of values from String to Integer/numeric datatype. If you have any queries, please comment below, we are always there to help you. Hope you found this piece helpful.