There is a MSSQL function that check the value. Like ISNULL(), ISDATE() and ISNUMERIC(). I don't see a function that check for decimal. If there isn't any then is there an user-defined function for it? I need to be able to validate the string value for decimal before it get assigned to a decimal datatype or T-SQL will run into an error.
I'm using MS-SQL 2000...
Thanks...
When I run this code on a column of the type float or real it's ok, but not if the column is decimal, why?
this produces an error:
SELECT IsNull(column1,'') as column1
FROM mytable
---
this code works:
SELECT IsNull(column1,0) as column1
FROM mytable
---
The problem is that this query is used in a C++ environment to build an insert into another table. The query is build together in a CString.
C++ considers a CString to have ended if it encounters a NULL, therefore I need the check on all columns.
The error message I get is:
"Error converting data type varchar to numeric."
Why is it ok to use float or rel, but not decimal??
Hi All,
I have a table Saleshistory with 89481412 records. I have a field Sales of nvarchar type, all the records in this field are numeric, and the records are with 2 decimal places. I expect all the records to be ending at .00 (for example 5.00,345.00,25.00), but I want to verify if something is for example 5.6 etc.
So in short I want to check if any Salesrecords have more than zero values in the decimal. How can I check this.
Please help.
Thanks,
Zee
I have a text file I have to load an massage the data. There is a date field which is a varchar where I do a conversion. As of 7-19 the conversion to inline sql stoppped working. Look at example below
declare @dob varchar(08)
set @dob = '02291956'
select convert(datetime,Right(@dob,4) + Left(@dob,4)) as dob
select convert(datetime,Right(dob,4) + Left(dob,4)) as dob from marylanddoc
The 1st statement works, however the select statement now fails with a conversion failure. ANyone seen this pop up. Just as an FYI, I do have 20 records that were processed with a leap year 0229 date that were created by this process before 7/19
IS THERE AN EQUIVALENT FUNCTION OF ISDATE IN SQL SERVER.?
Funnyfrog
Hi,
I have a package with a script task. I need to check incoming values from a file to make sure they're dates. I read the file in as one line, then create output columns. Hence, that is why I refer to the AcquistionDate field as array value 5, as this is the 6th column in the file.
In my connection manager, output columns, I have AcquisitionDate field defined as a STRING, length 10. In the flat file, dates look like this: "2007-08-23". I remove the "" with the String.Empty.
So, then I do an IsDate check.
Here is my code:
'Check date columns for date values
If IsDate(rowValues(5)) Then
Row.AcquisitionDate = rowValues(5).Replace("""", String.Empty)
Else
Row.AcquisitionDate_IsNull = True
End If
Problem is, IsDate is evaluating to FALSE. That is, execution goes to the Else portion, and sets all the AcquistionDates to NULL.
What am I doing wrong here?
Thanks
I am importing data from a legacy system. The date column is a varchar and in the destination DB it is a proper DateTime.
That said I have invalid data and need to clean / accommodate for it as I go. What I need is if the date is valid then insert it, if not then ue a default of '01/01/1900' I am doing the following and it is not working.
CASE
WHEN isDATE(RatifiedDate) = 1 THEN RatifiedDate
WHEN isDate(RatifiedDate) = 0 THEN '01/01/1900'
END as RatDate
<hr noshade size='1' width='250' color='#BBC8E5'>
I have a column which is current stored as nvarchar(10), and all of the enteries are either NULL or mm/dd/yyyy.
I am trying to convert the column to smalldatetime using CONVERT or CAST and each time I get an arithmetic overflow error message.
I also tried selecting just the enteries with ISDATE()=1 and converting those to smalldatetime, and still got an arithmetic overflow message.
(I've run ISDATE() several times on the column, and the only rows without ISDATE()=1 are those which have NULL values).
help!
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
strange thing I just ran into, not sure if this is a bug or what ... but pretty annoying.
In MS SQLServer 2000 :
SELECT (ISNUMERIC('0E010101'))
returns "1"
but
SELECT CAST ('0E010101' AS numeric)
returns "Error converting data type varchar to numeric"
any idea?
hi,
i am migrating data from a legacy system with a not nice front-end.
as a result, i have all sorts of garbage stored on the tables.
i
am trying to convert values from varchar(12) to float, but i have an
error during selecting data that says that data can not be converted
eventhough i am using the ISNUMERIC() function to check.
case when
isNumeric( myCol01 ) = 0 then null
else
convert( float , myCol01 )
end
but my error occours when ISNUMERIC() encounters the value '. ' ; that is a dot with spaces after it.
try the expression below;
SELECT
myValue = '. '
, is_numeric = ISNUMERIC('. ')
, converted = CONVERT( FLOAT , '. ')
has anyone got any idea how to work around this?
nicolas
Hi,
I have a table described as follows:
TableA
GrpDate DateTime;
grpMiscError varchar(1);
Ex Data:
2/1/2008 1
2/1/2008 1
2/1/2008 0
2/1/2008 x
2/1/2008 0
The grpMiscError can contain 0, 1 or x only. I need to sum up this column for all the zeros by a particular date.
I have the following but doesn't work:
SELECT
SUM(CASE ISNUMERIC(grpMiscError) WHEN 0 THEN 1 ELSE 0 END)AS MiscError
FROM TableA
WHERE GrpDate = '2/1/2008'
I get back an answer of 1 MiscError instead of 2
What am I doing wrong here?
Thanks,
J
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
View 2 Replies View RelatedHello.
My database stores the decimals in Spanish format; "," (comma) as decimal separator.
I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.
Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:
Error converting data type varchar to numeric
Any knows how to resolve.
Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.
Hello!
I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?
Thanks for giving me any advice,
Ziga
Hi all,
I have never seen this problem in SQL Server 7. The isnumeric() returns true for non-numeric data, as show in the following example:
declare @x as varchar(5)
select @x = '00d01'
select isnumeric(@x)
I would expect the isnumeric() function to return false. Can anyone give a reason why this should occur.
Does SQL Server think that this is a hex value and is performing an implicit conversion? If so how horrible...
Nick
The isnumeric function returns 1 in examples where it should not:
select isnumeric('1D9') returns 1.
Can anyone explain this?
Hello,
I would like to perform a delete statement on one of my tables.
I have a code column that has data in the form 011-234-12
and at some point in this column the data is like R0 or D1
I want to delete the rows where the code data is R0 or D1, E3, etc...
I would like to know how to create an SQL Command for Sql Server 7 that would delete from Soumission_detail where:
first left char is not Numeric in the code column.
Thanks.
HiHere's the problem:I need to search a postcode database by the first one or two letters.Problems occur for example when i want to search north London postcodes (N) when using:postcode LIKE @postcode + '%' As this picks up everything beginning with N, eg, NG for Nottingham, or NE for Newcastle. So i need a like statement which searches for the first one or two digits followed by a number!I've found the ISNUMERIC() function but not sure what the best way to use it with the like statement - or even if there is a better way altogether - can you use regular expressions in MSSQL?thanks
View 4 Replies View Relatedsneaky, sneaky, sneaky
ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.
thanks, but which one??
numeric as far as float is concerned, is not the same thing as numeric as far as money is concerned
create table isnumerics
( id integer not null identity
, txtfld varchar(11)
)
insert into isnumerics (txtfld) values ( '1' )
insert into isnumerics (txtfld) values ( '937' )
insert into isnumerics (txtfld) values ( '937.0' )
insert into isnumerics (txtfld) values ( '$937' )
insert into isnumerics (txtfld) values ( '$937.00' )
insert into isnumerics (txtfld) values ( 'free' )
insert into isnumerics (txtfld) values ( '.50' )
insert into isnumerics (txtfld) values ( '1,000' )
insert into isnumerics (txtfld) values ( '' )
select id
, txtfld
, isnumeric(txtfld)
from isnumerics
111
29371
3937.01
4$9371
5$937.001
6free0
7.501
81,0001
90
select id
, txtfld
, isnumeric(txtfld)
, case when isnumeric(txtfld) = 1
then cast(txtfld as money)
else cast(null as money)
end as case1
from isnumerics
1111.0000
29371937.0000
3937.01937.0000
4$9371937.0000
5$937.001937.0000
6free0
7.501.5000
81,00011000.0000
90
select id
, txtfld
, isnumeric(txtfld)
, case isnumeric(txtfld)
when 1
then cast(txtfld as money)
else cast(null as money)
end as case2
from isnumerics
1111.0000
29371937.0000
3937.01937.0000
4$9371937.0000
5$937.001937.0000
6free0
7.501.5000
81,00011000.0000
90
select id
, txtfld
, isnumeric(txtfld)
, case isnumeric(txtfld)
when 1
then cast(txtfld as float)
else cast(null as float)
end as case2
from isnumerics
1111.0
29371937.0
3937.01937.0
6free0
7.5010.5
the others got "Error converting data type varchar to float"
no, there wasn't a question here, but yes, i'd love to hear your comments
Hi,
I'm casting a varchar field to a decimal field using the format
CASE ISNUMERIC(GrossMktCapGbp)
WHEN 1 THEN CONVERT(DECIMAL(18,6),GrossMktCapGbp)
ELSE NULL
end
Thinking this would ensure that any spurious rows got set to null.
However I had a problem with some values that were set to '.', it seems that isnumeric thinks these are numbers but casting them to decimal produces an error.
SELECT ISNUMERIC('.')
SELECT CAST('.' AS DECIMAL(18,6))
Should I have been doing something different in my check possibly.
Sean
=iif(
IsNumeric(Fields!Accreditation.Value),
Int(Fields!Accreditation.Value),
Fields!Accreditation.Value
)
The above expression seems to work fine if Fields!Accreditation.Value is a number. However, if Fields!Accreditation.Value is not a number, it gives an #Error. Why is the true part evaluated when the expression is false?
I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl
Been meaning to post this for a while. It does a very limited job of only allowing [0-9], but could be extended to allow negative numbers, or numeric values that are suitable for numeric types other than INT, but avoiding the pitfalls of IsNumeric() which might allow through data not suitable for some of the numeric datatypes
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
(
-- String to be tested - Must only contain [0-9], any spaces are trimmed
@strINTvarchar(8000)
)
RETURNS int-- NULL = Bad INT encountered, else cleanedup INT returned
/* WITH ENCRYPTION */
AS
/*
* kk_fn_UTIL_IsINTCheck 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 valueValid integer
*NULLBad parameter passed
*
* HISTORY:
*
* 30-Sep-2005 Started
*/
BEGIN
DECLARE@intValueint
SELECT@strINT = LTRIM(RTRIM(@strINT)),
@intValue = CASE WHEN @strINT NOT LIKE '%[^0-9]%'
THEN CONVERT(int, @strINT)
ELSE NULL
END
RETURN @intValue
/** TEST RIG
SELECTdbo.kk_fn_UTIL_IsINT('123'),IsNumeric('123')
SELECTdbo.kk_fn_UTIL_IsINT(' 123 '),IsNumeric(' 123 ')
SELECTdbo.kk_fn_UTIL_IsINT('123.'),IsNumeric('123.')
SELECTdbo.kk_fn_UTIL_IsINT('123e2'),IsNumeric('123e2')
SELECTdbo.kk_fn_UTIL_IsINT('XYZ'),IsNumeric('XYZ')
SELECTdbo.kk_fn_UTIL_IsINT('-123'),IsNumeric('-123')
SELECTdbo.kk_fn_UTIL_IsINT('-'),IsNumeric('-')
**/
--==================== kk_fn_UTIL_IsINT ====================--
END
GO
Kristen
I would like to validate datatype using Derived Column.My data type are such as numeric(X,X),datetime,int, and varchar.How do I do this using Derived Column.Example if row does not qualify as ISNUMERIC()...throw it in ERROR table else send it to SUCCESS table.Any Idea ?
View 4 Replies View RelatedHello,
I have searched the forum, and have discovered that the DTS method using IsNumeric to check for numierc values (ActiveX) is not valid in SSIS. Most of what I have seen prescribes using the script component to handle this.
So formerly, I checked to see if a column was numeric. If it was, then I needed to use the numeric value as is, or in some cases, I needed to perform a calculation on the value and use the result. If the value was not numeric, then whatever the value was needed to be changed to zero.
Here is an example of how I would use the current value, or set the value to zero:
If IsNumeric(DTSSource("Col003")) Then DTSDestination("ADepTrnx") = CLng(DTSSource("Col003")) Else DTSDestination("ADepTrnx") = 0 End If
This is an example of how I would use the current value in a calculation, or set the value to zero:
If IsNumeric(DTSSource("Col012")) Then DTSDestination("AlliStdFee") = CLng(DTSSource("Col012"))/100 Else DTSDestination("AlliStdFee") = 0 End If
Does anyone have an example of how I would handle both situations in a script component?
Thank you for your help!
cdun2
Hello all!
I've wrote a small query for SQL 2005 and it's doesn't seem to work.
I have a table that contains two columns (X and Y), X is an int and Y is an nvarchar(50). I've populated this table with some data where Y contains numbers and some strings (e.g. "1", "2", "foo", etc). I've then got a view which only returns the rows where Y is numeric - now, I then query this table stating I only want numbers greater than 0 (i've casted the column) but this throws an error stating "foo" can't be casted. This is strange because the view doesn't return that.
What's going on? All of this works fine in SQL 2000 but not in SQL 2005 - looks like it's looking at the underlying table rather than the view. Sample code below to help you all out: -
Create Table
============
CREATE TABLE [dbo].[tblTest](
[X] [int] NOT NULL,
[nvarchar](50) NOT NULL
) ON [PRIMARY]
Insert Data
===========
INSERT INTO tblTest(X, Y) VALUES(1, '1')
INSERT INTO tblTest(X, Y) VALUES(1, '2')
INSERT INTO tblTest(X, Y) VALUES(2, 'foo')
INSERT INTO tblTest(X, Y) VALUES(2, 'bar')
Create View
===========
CREATE VIEW [dbo].[vwTest]
AS
SELECT X, Y
FROM dbo.tblTest
WHERE (ISNUMERIC(Y) = 1)
Finally
=======
SELECT X, Y
FROM dbo.vwTest
WHERE (CONVERT(int, Y) >= 0)
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'foo' to data type int.
Thanks in advance!
Does anyone else get the following result when running this query?
Select isnumeric('4D7')
-----------
1
(1 row(s) affected)
Does anyone know why this would return true for numeric?
Thanks,
Ray
I have a task (Derived Column Task) and I want to write something like this :
IsNumeric(aColumnOfString) == true ? "All numbers" : "there are some characters"
Here aColumnOfString can be something like "123a5" or 12345". I do not want to simply check if the left-most character is a number or not. I want to check the entire expression and return me a TRUE or false.
A TRUE is returned if the entire expression contains ONLY numbers, and FALSE otherwise.
I read some posting using regular expression. But that is not a solution for this situation.
Anyone knows how to accomplish this, please help!
I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?
View 6 Replies View RelatedHi all,
I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.
e.g.
‚¬1,500,123.00 to ‚¬1.500.123,00
Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.
Any help would be much appreciated
Thanks!
Hi - Please excuse me if this is really simple, but I'm fairly new to this lark.
My (made up) code is below... I'd be grateful for any pointers.
insert into [tblInvoices]
(full_period,
supplier_no,
account_code,
tran_amount,
function)
select
full_period,
supplier_no,
account_code,
tran_amount
case
when substring(account_code,1,2) = 'FY' then '-'
when isNumeric(account_code) then left(account_code, 2)
when not isNumeric(substring(account_code,1,2)) then left(account_code, 1)
else 'oops'
end as function,
from
tblLoadMSV900_i
end
Is this even close?
I'm using a stored proc to insert the data from tblLoadMSV900_i into tblInvoices and at the same time insert some data into the function field.
In plain english I want make sure that:
If the first 2 chars of account_code are 'FY' then function='-',
If the first char of account_code is numeric then function=left(account_code, 2),
If the the first char is not numeric (and if first two chars are not 'FY' i.e. first char could be 'F') then function=left(account_code, 1)
And there's plenty more where this came from! But if I can crack this with your help then I should have a better idea about the rest of the proc.
Thanks
Sara
I found this to work:
SELECT uri, evFieldUri, evFieldVal
, CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV
[Code] ....
It Returns:
uri         Â
evFieldUri         Â
evFieldVal         Â
(No column name)
224016Â 3267Â Â Â Â Â Â
+000089243829 89243829
224019Â 2717Â Â Â Â Â Â
+000089243825 89243825
224472Â 3333Â Â Â Â Â Â
+000000000000000000000017Â Â Â Â 17
225052Â 3267Â Â Â Â Â Â
+000089243829 89243829
225055Â 2717Â Â Â Â Â Â
+000089243825 89243825
So, then I went back to:
SELECT uri, evFieldUri, evFieldVal
, CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV
[Code] ....
And it returns this error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
So, I tried again, and this worked…
SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal
AS BIGINT),
ISNUMERIC(evFieldVal)
FROM TSEXFIELDV WHERE URI
> 0 AND evFieldUri
IN ( SELECT URI
FROM TSEXFIELD WHERE exFieldFormat
IN (1,11))
I logged out and came back and tried again, and it still worked. So then I tried…
SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV
WHERE URI
> 0
[Code] ...
And it fails.