• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

How To Use SQL to Convert a STRING to an INT

November 25, 2017 by Andy Hayes Leave a Comment

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]

 

sql convert string to int

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:

sql convert string to int with convert function

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:

sql convert string to int execution 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 🙂

Filed Under: SQL Tips and Tricks Tagged With: mysql, sql server

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

Primary Sidebar

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©