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
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
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?
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!
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!
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
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...
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.
Below is the statement given in MicroSoft SQL Server migration documentation :
"When a view is defined with an outer join and is queried with a qualification on a column from the inner table of the outer join, the results from SQL Server and Oracle can differ. In most cases, Oracle views are easily translated into SQL Server views"
Please can anyone explain the above with some examples. I don't find such a 'CREATE VIEW' statement existsing in two DBs, which results in different result set. Am I wrong ?
Thanks,
Sam
Another tech was having the same problem with his sp which he also made into a macro. He was having the same problem as I was, he thought since he made the user the dbo owner that he woud have no problems...but since hes the one who created the db and the stored procedures when the user tried to access it he got that same error message. It went away after he went into the Enterprise manager and in the properties of the stored procedure under permission he gave the user EXEC permissions...And WAAALLLAAA problem solved :):):):)
View 10 Replies View RelatedAnyone else experience this? A developer just finished complaining about the performance of one of our databases. Well, he sent me the query and I couldn't understand why it was such a dog. Anyways I rewrote it. The execution plan is totally different between the two. I had no idea specifying the join made such a difference. First sql executed in 7 minutes that 2nd took 1 second. SELECT dbo.contract_co.producer_num_id, contract_co_statusFROM dbo.contract_co, dbo.v_contract_co_statusWHERE ( dbo.v_contract_co_status.contract_co_id = dbo.contract_co.contract_co_id ) AND contract_co_status = 'Pending' OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )SELECT dbo.contract_co.producer_num_id, contract_co_statusFROM dbo.contract_co INNER JOIN dbo.v_contract_co_status ON dbo.contract_co.contract_co_id = dbo.v_contract_co_status.contract_co_idWHERE contract_co_status = 'Pending' OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )
View 3 Replies View RelatedHi all,
I have a system in place currently that both a local and a live database, currently the sales database is kept in line with a home grown "syncronise" script that controls keeping the two tables (live and local) in sync with the use of Local and Remote ID'sFor example;Table "Sales"ID - IntRemoteID - IntVarious Data fieldsThe 'Live' System writes new records using the RemoteID field, whilst the local inserts new records using the ID field. The Sync script copies the data up/down and assigns the corresponding RemoteID/ID's to the records as they are copied accordingly.Its been decided that this process must change, in favor of Two Way Replication. I have been tasked with finding the solution to the safe handling of ID's as theres a danger that two people can be writing a record (one live, one local) and possibly create the same ID's - i know that replication has its own ID's and could probably manage this well on its own, but these 'Internal' ID's have to be unique due to business rules.So i have been considering two possibilities to address this problem,1. Staggering the ID's - for instance Live ID's begin at 1,000,001 and local ID's begin at 1.2. Having a controlling ID table which provides the next IDBoth options have their advantages and disadvantages, with option 1 we will run into a problem later when the local ID's reach the 1 million mark (a few years away, i admit) and option 2 will need careful locking/transactional code to ensure the same ID isnt given twice.Does anyone have experience with this kind of issue? or could suggest an alternative approach to this problem? Thanks in advance!
We've had an interesting server issue here since about the start of the year, where the dual processor Netfinity server we've been running SQL 7 on has been holding steady at almost exactly 50% CPU utilization, give or take about 5%-10%. Disk utilization in all areas is relatively low. In fact, the server will do this right after a fresh reboot, with no users accessing. The really interesting part is that a graph of the two processors mirror each other. When the utilization of one goes up, the other goes down, almost perfectly. The two processors are the same speed, but their firmware build is different. IBM says it's not a problem. What say ye?
View 1 Replies View RelatedIf Query1 gives 1 row output as:
customer_id
1234
-------------------
Query2 gives 1 row output as:
product_id
8970
-------------------
How do we get the above results as a single result set of 2 columns side by side? as follows:
customer_id product_id
1234 8970
Is that possible in a single select statement??
Query1 and Query2 both are select statements on the same table with different where clauses.
Sheila.
Hi ! I am facing an interesting problem for one of my databases. When I double click on a particular table in Enterprise Manager, the Manage Tables window shows me only one or two fields from the table. (it does not show me all the fields). When I run sp_help <tablename> in isql, all the information is displayed. Why is there such inconsistency. It happens only on one particular database. I have run DBCC commands but still no help. Does anyone have any ideas on this. Thankx,
View 3 Replies View RelatedWe're having to work with some legacy data. The tables in the so-called database seem to have way more nulls than actual data. One table appears to have around 100 or more columns in it. It has close to 40,000 rows.
This Db has pretty much 0 normalization present.
IOW, your worst nightmare.
Is there a way we could run a query that would return the total number cells inthat contain NULL and another that could return the total number of data-bearing cells so we could come up with a % or a ratio.
Hi Folks,
This is a create table statement in DB2 z/ODS and OS/390 ( mainframe versions ) :
create table test(sno int) IN DATABASE database1 AUDIT NONE DATA CAPTURE NONE
This will create a table in the named database 'database1'.
1) In SQL Server, is there any equivalent for this 'IN DATABASE' syntax in DB2 ?
2) 'Data capture none' will not store any extra info about the data replication in the log files. Is there any eqiuivalent for this in SQL Server ?
Thanks,
Sam
In this SQL query I get en error like this:Incorrect syntax near ','.SELECT SUM(mCount * mPrice), CONVERT(DATEPART(yyyy, mDate), varchar) + '.' + CONVERT(DATEPART(mm, mDate), varchar) AS DateSoldFROM salesWHERE mDate BETWEEN '9/10/2007 0:0:0' AND '1/10/2008 23:59:59'I think you it isnt necessary to show values in the table because it is a syntax error but I dont undertand how it can be incorrect...Thanks for help...Saren Taşçıyan
View 14 Replies View RelatedImagine a table in Microsoft Sql Server 2000 named Pictures with threefields. A primary key ID(int), a Name(nvarchar) and a Picture(image)field.Lets put some records into the table. The first two fields as expectedwould take an int and a string. The third field is of type image.Instead of putting a bitmap in this field lets place an xml documentthat has been streamed into a byte array. The xml document woulddescribe the Picture using say lines. If the picture was that of asquare we would have four lines in the xml document. You could think ofthe xml document as something similar to vector graphics but thedetails are not relevant. The important fact is that the contents ofthe image field is NOT a bitmap but a binary stream of an xml document.Now imagine we have a reporting tool like Crystal Reports that can beused to report on this database table. Imagine we create a report byusing the three fields mentioned above. As far as Crystal is concernedthe first field is an int, the second a string and the third an image.If our table had ten records and we preview the report we like to see10 entries each consisting of an ID, Name and a Picture.This can only happen if the image field contains a Bitmap, but asmentioned above the field contains an xml document.Now my question...Can we write something in SQL Server 2000 (not 2005) to sit between thetable and Crystal Reports so to convert the XML document to a bitmap.The restriction is that we cannot use anything but sql server itself.The client in the above case has been Crystal Reports but it could beanything.I know SQL Server 2005 supports C# with access to the .NET frameworkwithin the database. Unfortunately, I am not using SQL server 2005.Some people have suggested the use of User Defined Functions and TSQL.I like to know from the more experienced SQL Server people if what I amtrying to achieve is possible. Maybe it has not been done but is itpossible?Any suggestions would be greatly appreciated...Many Regards
View 5 Replies View Related
Hi All,
When I wanted to see ONLY today€™s records on the sql server, it took 5 minutes.
When I wanted to see records including today€™s records on the sql server, no problem. (19 Sep. is a past date which is working properly)
It is very interesting it takes 5 minutes to sort the lastday there is nothing wrong with the other days.
I ran DBCC INDEXDEFRAG method but no change anything.
I am wondering about something, everyday at 22:00 pm we are getting backup. Is this process creating a difference between before getting the back up and after got the backups?
It is really interesting issue. I do not know what I can say. It seems there is an issue with today€™s records. May be there are some locks on the today€™s records. I do not know. Why does not cause any issue for old dates but for current day ?
SELECT DISTINCT CallIDChar, ANI, DNIS, CallerName, AgentName, CallQueue, AgentID, CallType, CallStartTimeStamp, CallEndTimeStamp, CallDuration
FROM (SELECT MAINTBL.*, DRTTABLE.CallDuration AS CallDuration
FROM CallInfo AS MAINTBL
JOIN ( SELECT CallID, DateDiff(second, min(CallStartTimeStamp), max(CallEndTimeStamp)) as CallDuration
FROM CallInfo
GROUP BY CallID
) AS DRTTABLE ON MAINTBL.CallID=DRTTABLE.CallID
) as CallInfo
WHERE CallIDChar = CallIDChar
AND (CallStartTimeStamp >= '2007-09-20T00:00:00')
AND (CallStartTimeStamp <= '2007-09-20T23:59:59')
What can be reason for this issue and how can I solve it ?
Thank you in advance,
Melih
I have a very simple SSIS package that imports data from ODBC source. There are two "Time" columns in source, but When this table is created in SQL2005, it give me "bigint"!!!
How can this happen?
I tried to import data to SQL2000, those two columns are created as "smalldatetime" properply.
Thanks.
I am making a report in Visual Studio..Now my question is,is there anyway that i can add a tab control for my rdl??Hope to here from anyone out there with a know how..Thanks guys!!
View 13 Replies View Related