Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Check the value of a decimal type column

Posted on 2007-11-16
7
Medium Priority
?
226 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

963 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