converting varchar to numeric values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

converting varchar to numeric values

Author

Topic

sl9799

4 Posts

Posted - 2005-10-31 : 17:25:47

Not sure how I can convert varchar to numeric values in SQL Server? Trying to do so in Design and get error:
Unable to modify table.
ODBC error: [Microsoft][ODBC Server Driver][SQL Server] Error converting data type varchar to numeric.
Any suggestions?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-10-31 : 17:47:17

You're going to have to fix the non-numeric values first. Select the records from the table WHERE ISNUMERIC(column)=0. That will give you back the values you need to fix.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

sl9799

4 Posts

Posted - 2005-10-31 : 18:07:17

I ran this sql statement...

DELETE FROM dbo.hospin3 WHERE ISNUMERIC(col013)=0

...and got the following result
(0 row(s) affected)

...and yet I still get the error under design:
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to numeric.

...this is getting frustrating.

Any Microsoft articles that address this glitch in SQL Server 2000?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-10-31 : 18:22:10

Notice where this script breaks. It's not a "glitch" in SQL Server 2000.


USE Northwind
GO

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'test_numeric_conversion' AND xtype = 'U')
BEGIN
	DROP TABLE test_numeric_conversion
END

CREATE TABLE test_numeric_conversion(col1 VARCHAR(55))

INSERT test_numeric_conversion(col1)
	SELECT '0001' UNION ALL
	SELECT '123414' UNION ALL
	SELECT '01.12398' UNION ALL
	SELECT '1.109481' UNION ALL
	SELECT '4387198173' UNION ALL
	SELECT '438,719,817.3' UNION ALL
	SELECT '' UNION ALL
	SELECT NULL

SELECT col1, ISNUMERIC(col1) FROM test_numeric_conversion
GO
DELETE test_numeric_conversion WHERE ISNUMERIC(col1) = 0
GO
ALTER TABLE test_numeric_conversion ALTER COLUMN col1 NUMERIC
GO
SELECT col1, ISNUMERIC(col1) FROM test_numeric_conversion
GO

If you do this:


USE Northwind
GO

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'test_numeric_conversion' AND xtype = 'U')
BEGIN
	DROP TABLE test_numeric_conversion
END

CREATE TABLE test_numeric_conversion(col1 VARCHAR(55))

INSERT test_numeric_conversion(col1)
	SELECT '0001' UNION ALL
	SELECT '123414' UNION ALL
	SELECT '01.12398' UNION ALL
	SELECT '1.109481' UNION ALL
	SELECT '4387198173' UNION ALL
	SELECT '438,719,817.3' UNION ALL
	SELECT '' UNION ALL
	SELECT NULL

SELECT col1, ISNUMERIC(col1) FROM test_numeric_conversion
GO
DELETE test_numeric_conversion WHERE ISNUMERIC(col1) = 0
GO
SELECT col1, REPLACE(col1,',','') FROM test_numeric_conversion
GO
UPDATE test_numeric_conversion
SET col1 = REPLACE(col1,',','')
GO
SELECT col1 FROM test_numeric_conversion
GO
ALTER TABLE test_numeric_conversion ALTER COLUMN col1 NUMERIC
GO
SELECT col1, ISNUMERIC(col1) FROM test_numeric_conversion
GO

then it will work.

Having said that, are you sure you want to convert to numeric. Pay real close attention to what's happening here.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2005-11-01 : 01:07:00

This function is more strict about what it allows as an "integer"

Try

SELECT col013 FROM dbo.hospin3 WHERE dbo.kk_fn_UTIL_IsINT(col013) IS NULL

and see what you get

Kristen


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_IsINT]') 
		AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION dbo.kk_fn_UTIL_IsINT
GO

CREATE FUNCTION dbo.kk_fn_UTIL_IsINT
(
	@strINT		varchar(8000)	-- String to be tested - Must only contain [0-9]
)
RETURNS int	-- NULL = Bad INT encountered, else cleanedup INT returned
/* WITH ENCRYPTION */
AS
/*
 * kk_fn_UTIL_IsINT	Check that a String is a valid INT
 *			SELECT dbo.kk_fn_UTIL_IsINT(MyINTColumn)
 *			IF dbo.kk_fn_UTIL_IsINT(MyINTColumn) IS NULL ... Bad INT
 *
 * Returns:
 *
 *	int value
 *	NULL		Bad parameter passed
 *
 * HISTORY:
 *
 * 30-Sep-2005 KBM  Started
 */
BEGIN

DECLARE	@intValue	int

	SELECT	@strINT = LTRIM(RTRIM(@strINT)),
		@intValue = CASE WHEN @strINT NOT LIKE '%[^0-9]%'
			THEN CONVERT(int, @strINT)
			ELSE NULL
			END
	RETURN @intValue

/** TEST RIG

SELECT	dbo.kk_fn_UTIL_IsINT('123'),	IsNumeric('123')
SELECT	dbo.kk_fn_UTIL_IsINT(' 123 '),	IsNumeric(' 123 ')
SELECT	dbo.kk_fn_UTIL_IsINT('123.'),	IsNumeric('123.')
SELECT	dbo.kk_fn_UTIL_IsINT('123e2'),	IsNumeric('123e2')
SELECT	dbo.kk_fn_UTIL_IsINT('XYZ'),	IsNumeric('XYZ')

**/
--==================== kk_fn_UTIL_IsINT ====================--
END
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-01 : 23:40:11

Sometimes ISNUMERIC may be ineffecient
http://aspfaq.com/show.asp?id=2390

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources