Ali,

CREATE TABLE numbers (col1 VARCHAR(10));
INSERT INTO numbers VALUES ('12345');
INSERT INTO numbers VALUES ('123E5');
INSERT INTO numbers VALUES ('12/45');
INSERT INTO numbers VALUES ('1 345');

SELECT *
FROM numbers
WHERE col1 NOT LIKE '%[^0123456789]%'

Hope that helps,
Rich


[quoted text, click to view]

You can use LIKE to test for only digits.

CREATE TABLE #MyTable
(
MyColumn varchar(10) NOT NULL
)

INSERT INTO #MyTable
SELECT '1'
UNION ALL
SELECT ' 1'
UNION ALL
SELECT ' 1 '
UNION ALL
SELECT '1 '
UNION ALL
SELECT '1,0'
UNION ALL
SELECT '1.0'
UNION ALL
SELECT '$1,0'
UNION ALL
SELECT '1E0'
UNION ALL
SELECT '1D0'

SELECT MyColumn
FROM #MyTable
WHERE ISNUMERIC(MyColumn) = 1

SELECT MyColumn
FROM #MyTable
WHERE MyColumn NOT LIKE '%[^0-9]%' AND MyColumn <> ''

DROP TABLE #MyTable


--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

[quoted text, click to view]

Hi,

The following sql command fails:

select job_no,job.udf1,cast(job.udf1 as decimal ) from job where
IsNumeric(job.udf1)=1

Because if job.udf1 contains '.' or ',' then IsNumeric(job.udf1) is equal
with 1 but cast(job.udf1 as decimal ) returns error.

Obviously the following commad also returns same error :

select job_no,job.udf1,
CASE IsNumeric(job.udf1) When 1 Then cast(job.udf1 as decimal ) End
FROM job


So if we have 10000 rows and just one row contains bad value, the command
fails. I want bad values like '.' or ',1' be considered as zero.


What would be the safest way to convert varchar to numeric values?

Any help would be apprecited,
Ali

[quoted text, click to view]

If udf1 contains numeric values, how about updating the structure of the
table so that only valid numeric values can get in there in the first place?

A

UDF1 is a user defined column and needs to have mixed values.



[quoted text, click to view]

Hi Ali,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.

First of all, I would like to thank all for their help!

From your description, I notice that you received the 8114 error when you converted a Varchar
column to Numeric format.

Based on my research, CAST or CONVERT a varchar column to a Numeric format
DIRECTLY, which used to work in SQL Server 6.5, are no more working in 7.0 and 2000. For
example: Select CONVERT(float, '21ABC') would return 21.0 as output in 6.5.

However, the same statement fails in 7.0 and 2000 with the following error as yours:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

The SAFE way to workaround this problem is to make sure you are passing valid data to the
CAST or CONVERT function. I recommend you apply the following suggestions to filter the
data and send the valid data to CAST function:

1. First use IsNumeric(job.udf1) to filter the Varchar data
2. Filter the marks such as a SINGLE '.' and ',' in the WHERE clause. You can perform like that:
WHERE IsNumeric(job.udf1)=1 and job.udf1 <> '.' and job.udf1 <> ','
3. Replace other delimiters (such as comma ',') with pot '.' so that it is valid when it passes to
the CASE function. You can do the following: REPLACE(job.udf1, ",",".").

On the other hand, you can also filter the Integer data by means of LIKE/NOT LIKE in the
WHERE Clause like that:

SELECT job_no,job.udf1
FROM job
WHERE job.udf1 NOT LIKE '%[^0-9]%'

Does that answer your question Ali? Please apply my suggestion above and let me know if it
helps you resolve your problem. If there is anything more I can assist you with, please feel free
to post it in the group.


Regards,
Billy Yao
Microsoft Online Partner Support

----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Why not have UDFNumeric and UDFVarchar? Seems silly to kludge it up in
order to save a column.




[quoted text, click to view]