Solved

Check the value of a decimal type column

Posted on 2007-11-16
7
190 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

770 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