Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

mySQL - CAST VARCHAR(255) TO INT

Posted on 2014-08-28
12
Medium Priority
?
1,017 Views
Last Modified: 2014-08-28
How do you cast a varchar to int  ???????

CAST(`TitleId` AS INT)   AS `TITLEID`
0
Comment
Question by:JElster
  • 6
  • 3
  • 3
12 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40290665
For starters, you gotta make sure that the value in the varchar is numeric, then validate that the number is an integer...

-- Int
Declare @val varchar(255) = '7'
SELECT CASE WHEN ISNUMERIC(@val) = 1 AND CAST(@val as numeric) = FLOOR(@val) THEN CAST(@val as int) ELSE NULL END

-- Not numeric
SET @val = 'banana'
SELECT CASE WHEN ISNUMERIC(@val) = 1 AND CAST(@val as numeric) = FLOOR(@val) THEN CAST(@val as int) ELSE NULL END

-- Numeric, but not an int.  How do you want to handle these? 
SET @val = '1.98'
SELECT CASE WHEN ISNUMERIC(@val) = 1 AND CAST(@val as numeric) = FLOOR(@val) THEN CAST(@val as int) ELSE NULL END

Open in new window

0
 
LVL 1

Author Comment

by:JElster
ID: 40290682
They are all numeric
whats wrong with this

SELECT CAST(TitleId AS Int)   AS TITLEID FROM  books
0
 
LVL 1

Author Comment

by:JElster
ID: 40290695
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Declare @val varchar(255) = '7' SELECT CASE WHEN ISNUMERIC(TitleId) = 1 AND CAST' at line 1
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40290748
They are all numeric
OK, then just use them as numbers.  Is there a scripting language involved here?  If it's PHP, loose data typing is your friend.  All MySQL values come back from the DB engine into PHP as strings, but numeric values will be converted automatically.
0
 
LVL 1

Author Comment

by:JElster
ID: 40290776
Just trying to run this in mySQL Workbench - looking for the SQL to cast nvarchar to int.
0
 
LVL 1

Author Comment

by:JElster
ID: 40290778
Just trying something like this

SELECT CAST(TitleId AS Int)   AS TITLEID FROM  books
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40290781
>Error Code: 1064. You have an error in your SQL syntax;
My code was T-SQL, which may be different from MySQL

>whats wrong with this
>SELECT CAST(TitleId AS Int)   AS TITLEID FROM  books
Nothing as long as every single TitleID is convertable to an Integer, but if there is a single value that's not, such as 1.98 or banana, then it will throw a hard error, and it's better to handle that in code then take your chances and deal with the impacts of errors.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40290798
I believe you can also multiply by one to cast a numeric string as a number.  I seem to recall having to do this once with strings that were padded by different numbers of leading zeros.
0
 
LVL 1

Author Comment

by:JElster
ID: 40290805
mySQL doesn't seem to like T-SQL


here's what I get
12:39:15      SELECT CAST(TitleId AS Int)   AS TITLEID FROM  books      Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Int)   AS TITLEID FROM  books' at line 1      0.000 sec
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40290816
@Ray - A couple of times I was able to use the integer division \ , such as 3.98 \ 1 (which is 3) does not equal 3.98 / 1
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 40290863
Please see near line 130: http://iconoun.com/demo/temp_jelster.php

<?php // demo/mysqli_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// THIS SCRIPT DEMONSTRATES SOME OF THE BASICS OF MySQLi


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// CREATE AN ARRAY OF TEST DATA
$test_names_arrays = array
( array( "fname" => "103"    )
, array( "fname" => "0103"   )
, array( "fname" => "-17"    )
, array( "fname" => "6.023"  )
, array( "fname" => "+44"    )
, array( "fname" => "John"   )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT          NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(255) NOT NULL DEFAULT ''
)
"
;

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_fn  = $mysqli->real_escape_string($person['fname']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( fname ) VALUES ( '$safe_fn' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }


    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_fn</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// CONSTRUCT THE QUERY TO CAST THE VARIABLE
$sql = "SELECT fname, CAST(fname AS SIGNED) AS my_num FROM my_table ORDER BY fname";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}

Open in new window

0
 
LVL 1

Author Closing Comment

by:JElster
ID: 40290874
Ok.. Need to cast as SIGNED
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month10 days, 14 hours left to enroll

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question