Solved

Check the value of a decimal type column

Posted on 2007-11-16
7
183 Views
Last Modified: 2010-04-21
I have a SQL table column X which hold a data type varchar2

I have the table populated with some records.

and the data looks like

21.98
23.87
34.12 OFF
12.23

etc

I want to find the row that does not hold a value price. How can i find it.
I need the row 34.12 OFF to be retrieved for the illustration.
0
Comment
Question by:TECH_NET
  • 4
  • 3
7 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20301274
Select X From myTable where IsNumeric(X) = 0

This will catch any non numeric rows.
0
 

Author Comment

by:TECH_NET
ID: 20301325
It works great. Exactly what i wanted. Now another question.

one of the value of the price column was

Skil 7.2-volt Lithium-Ion Power Wrench -

How can i update the another column Y, with the value of the PRICE, and if the column Y has a value , can i append the value of the PRICE column to value of column Y.

I increased the points to 500
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20301373
Update myTable Set Y = IsNull(Y, '') + IsNull(X, '')
Where IsNumeric(X) = 0

The IsNulls are there to catch any instance where you don't have an existing value in Y or in the event the data in column X is null.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20301376
My code will update all of your Y columns.  Are you wanting to do this on a row-by-row basic?
0
 

Author Comment

by:TECH_NET
ID: 20301585
yes, i want to update only rows where the column of price is not numeric,
0
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 500 total points
ID: 20301602
Then my code will work as written.  Obviously, if you wanted to do it on specific rows you'd include your PK in the Where clause.
0
 

Author Closing Comment

by:TECH_NET
ID: 31409641
Thank you for your quick responses.
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Where not exists in same table 3 53
SQL Date from a string 4 58
Query - which index being used? 2 48
CREATE DATABASE ENCRYPTION KEY 1 59
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now