Hi again all,
I have a small issue. Here's an example dataset :
F1 F2 F3
1 0.58 Hi
2 0.70 Hello
3 Fail Bye
4 <Null> Hi
When I write this statement :
SELECT SUM(CONVERT(DEC IMAL(16,8),F2)) MySum
FROM T1
WHERE IsNumeric(IsNul l(F2,'X'))=1
I get "Cannot convert a Varchar value to Numeric" error. From what I
understand, it somehow tries to convert to a decimal(16,8) BEFORE filtering
the nulls and the non-numeric out. (Keep in mind that the actual table has
over 1.5Million records).
Any idea on how to get around that ?
Thanks,
Michel 1 20622
Michel (Mi****@askme.c om) writes: F1 F2 F3 1 0.58 Hi 2 0.70 Hello 3 Fail Bye 4 <Null> Hi
When I write this statement :
SELECT SUM(CONVERT(DEC IMAL(16,8),F2)) MySum FROM T1 WHERE IsNumeric(IsNul l(F2,'X'))=1
I get "Cannot convert a Varchar value to Numeric" error. From what I understand, it somehow tries to convert to a decimal(16,8) BEFORE filtering the nulls and the non-numeric out. (Keep in mind that the actual table has over 1.5Million records).
This might help:
SELECT SUM(CASE WHEN isnumeric(F2)
THEN convert(decimal (16, 8)), F2
ELSE 0
END) MySUM
FROM T1
WHERE isnumeric(F2) = 1
Beware that isnumeric may give you false positives. Not all strings
that causes isnumeric to return 1 are convertible to decimal.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Bill |
last post by:
I'm getting the following error:
Disallowed implicit
conversion from data type varchar to data type money, table
'bill.dbo.cart', column 'price'. Use the CONVERT function to run this
query.
Does anyone understand this function and how to use it? I'm trying to
pull a quantity value from one table, multiply it times a price to get
a total cost, and then stick the product into the new cart table.
|
by: Dean G |
last post by:
I need to compare two values. one from a text field 'bid' and the other
from a field in an sql server database 'maxbid'.
The problem is the column in the database has decimal as its data type
and i'm getting a type mismatch. does anyone know how to convert 'bid'
into decimal from varchar? the field datatype doesnt necessarily have
to be decimal although i need two decimal places so it cant be an int.
Thanks,
Dean
|
by: meh |
last post by:
I have imported data from excel file. When data came to SQL table, the type
of AMOUNT column was varchar. I tried to convert and cast amount type of
amount column to number type but it does not allow me to convert.
What is the best way of importing data into SQL and type stays the same as
it was in excel file ?
Or anyone has any better solution, please let me.
Thanks.
|
by: BashiraInTrouble |
last post by:
Hi Friends,
I have tried almost everything but I cant seem to shrink the
transaction log.
Executing DBCC SQLPERF(LOGSPACE)
gives me this info:
Database Log Size (MB) Log Space Used (%) Status
MY_eems 368.49219 16.034182 0
I made a complete backup of the database and transaction log and then
executed this statement:
DBCC SHRINKFILE (MYeems_log, 1)
|
by: alan |
last post by:
in my project there are
5 textbox : tbOrderid, tbSeqNum, tbFoodCode, tbFoodDesc and tbPrice
9 button: btInsert, btdelete, btUpdate, btClear, btBind, btFirst,
btPrevious, btNext and btLast
i cannot do the insert and delete function. Can anybody help me? If
you need the program I can email to anyone who want to help me,
thanks!! ^^Alan
and the code is as follow:
| |
by: Robert Fitzpatrick |
last post by:
Using pl/pgSQL on 7.4.3, I have a varchar column called unit name in a table that can be numeric, of course. Most of the time, end users will put A, B, C, D, etc. or 101, 102, 103, etc. I am trying to write a function to determine the next available number after the first is given. For instance, so far I have a function that will determine 102 is next if 101 was used firstby using the int2() function to convert it first. But, of course, I get...
|
by: Jim C. Nasby |
last post by:
Is there any reason why there isn't a predefined cast to go from a
timestamp to a varchar? Is there a reason not to add one?
--
Jim C. Nasby, Database Consultant jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or...
|
by: angellian |
last post by:
Sorry to raise a stupid question but I tried many methods which did
work.
how can I conserve the initial zero when I try to convert STR(06) into
string in SQL statment?
It always gives me 6 instead of 06.
Thanks a lot.
|
by: gowrishankar |
last post by:
Hello,
I have to convert a alpha numeric value to a numeric value using query.
Is there a way to do it.
The column data type is Varchar and I am storing alpha numeric values to it.
I have to sort the column now. when I say order by it is not comming properly.
Need some solution to do it.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |