Using SQL to convert a string to an int is used in a variety of situations. You may have text data that you cannot alter at the source and you need to get some accurate answers from it. You may also have text data that you want to insert to an integer column. There could be other reasons too.
There is an easy solution to this and I will show you examples in both SQL Server and MySQL.
How to Convert a String to Number Using T-SQL in SQL Server
Let us create some data first to demonstrate this:
If I create some data like the following and then try to order it from lowest number to highest, the results are not output as I need them to.
Of course the database doesn’t know this and outputs them correctly as it is text data.
[sourcecode language=’sql’]
CREATE TABLE t1
(txt VARCHAR(10))
INSERT t1
SELECT ‘2’;
INSERT t1
SELECT ‘3’;
INSERT t1
SELECT ’11’;
INSERT t1
SELECT ‘1’;
SELECT *
FROM t1
ORDER BY txt;
[/sourcecode]
I need to use some additional sql to convert the string values to integers. So I will use the CONVERT() function to do this for me.
[sourcecode language=’sql’]
SELECT *
FROM t1
ORDER BY CONVERT(INT, txt);
[/sourcecode]
Now the results are output correctly:
You can see there is a slight change in the execution plans here with the conversion of the string data to a number happening in the second plan:
How to Convert a String to Number Using in MySQL
This is also possible with MySQL, the code above containing CONVERT() no longer works, instead it can be written as follows:
[sourcecode language=’sql’]
SELECT *
FROM t1
ORDER BY CONVERT(txt, SIGNED INTEGER);
[/sourcecode]
Ordering the above query without the CONVERT does not alter the execution plan.
I hope you found this post useful 🙂
Leave a Reply