Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews Databases Programming Interviews:BtrieveClipperData ModelingData StructuresDatabase AdministrationDatabase Administrator (DBA)Database AnalystDatabase DeveloperDB DevelopmentEDI/Data Integration ExpertFirebirdHierarchicalIBM DB2InformixJava DatabaseMariaDBMicrosoft Access DeveloperMongoDBMS SQL ServerMySQL ProgrammingNetworkNoSQLObject RelationalPostgrePostgreSQLProgressRDBMSRelationalSQLSQL AdministratorSQL and PL/SQLSQL Notification ServicesSQL server 2008SSRSStored ProcedureSybaseTeradata
Copyright © 2018. All Rights Reserved
MS SQL Server Interview Question:
How To Convert Character Strings into Numeric Values?
Submitted by: AdministratorSometimes 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 ');
Submitted by: Administrator
* 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 ');
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.