mySQL - CAST VARCHAR(255) TO INT

How do you cast a varchar to int  ???????

CAST(`TitleId` AS INT)   AS `TITLEID`
LVL 1
JElsterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
JElsterAuthor Commented:
They are all numeric
whats wrong with this

SELECT CAST(TitleId AS Int)   AS TITLEID FROM  books
0
JElsterAuthor Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Ray PaseurCommented:
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
JElsterAuthor Commented:
Just trying to run this in mySQL Workbench - looking for the SQL to cast nvarchar to int.
0
JElsterAuthor Commented:
Just trying something like this

SELECT CAST(TitleId AS Int)   AS TITLEID FROM  books
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
Ray PaseurCommented:
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
JElsterAuthor Commented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
@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
Ray PaseurCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JElsterAuthor Commented:
Ok.. Need to cast as SIGNED
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.