Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - CONVERT() - Converting Character Strings to Numeric Values

By: FYIcenter.com

(Continued from previous topic...)

How To Convert Character Strings into Numeric Values?

Sometimes you need to convert numeric values enclosed in character strings back to numeric values by using the CONVERT() function.

When converting character strings to values with CONVERT(), you need to remember two rules:

  • Leading and trailing space characters are ignored.
  • The input string must represent a numeric literal that matches the target numeric data type.
  • If the input string does not represent a numeric literal that matches the target numeric data type, you will get a conversion error.

The tutorial exercise below shows you how to use the CONVERT() function to convert strings to values:

SELECT CONVERT(NUMERIC(10,5), ' 12345.12 ');
GO
12345.12000

-- Input does not match the target data type
SELECT CONVERT(INT, '12345.12');
GO
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar 
   value '12345.12' to data type int.

-- Input does not match the target data type
SELECT CONVERT(NUMERIC(10,5), '12345.12E+00');
GO
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

-- Double conversion works
SELECT CONVERT(NUMERIC(10,5), 
   CONVERT(FLOAT(24), ' 12345.12E+00 '));
GO
12345.12012

SELECT CONVERT(FLOAT(24), ' 12345.12E+00 ');
GO
12345.12

(Continued on next topic...)

  1. What Is an Expression?
  2. What Are Arithmetic Operators?
  3. What Happens to an Arithmetic Operation with Two Different Data Types?
  4. How To Convert a Numeric Expression from One Data Type to Another?
  5. How To Convert Numeric Expression Data Types by Assignment Operations?
  6. How To Convert Numeric Expression Data Types using the CAST() Function?
  7. How To Convert Numeric Expression Data Types using the CONVERT() Function?
  8. How To Convert Character Strings into Numeric Values?
  9. What Happens When Converting Big Values to Integers?
  10. What Happens When Converting Big Values to NUMERIC Data Types?
  11. What Are the Mathematical Functions Supported by SQL Server 2005?
  12. How To Convert Numeric Values to Integers?
  13. How To Round a Numeric Value To a Specific Precision?
  14. How To Generate Random Numbers with the RAND() Function?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...