[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to search for blank entries

Posted on 2012-08-20
7
Medium Priority
?
421 Views
Last Modified: 2012-08-20
I have a table named tblSummerYouthGrid in SQL.  I need to check for all values in column "Monday" that have a blank entry (" ") and replace it with a "0"

The datatype in "Monday" is numeric
0
Comment
Question by:al4629740
  • 3
  • 2
  • 2
7 Comments
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 1000 total points
ID: 38312069
If the data type is numeric, there can't be entries with a space. Something is a bit off there. Anyway, you can check for empty, space or nulls in one go:
UPDATE tblSummerYouthGrid SET Monday=0
WHERE NOT ISNUMERIC(Monday)>0
0
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 1000 total points
ID: 38312083
Here ya go:

UPDATE tblSummerYouthGrid SET Monday = ISNULL(Monday, 0)
0
 

Author Comment

by:al4629740
ID: 38312224
This is the datatype  decimal(18, 2)

The user is leaving the field blank from the application and then it messes up the database.  It DOES leave the field in the table as blank
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 1000 total points
ID: 38312243
You should add a check on the insert. Something like:
If Not IsNumeric(MyTextBox.Text) Then
  MsgBox "Insert a valid number"
  'OR: MyTextBox.Text = 0
Else
  DoInsertFunction()
End If

This is for VB, but the theory is pretty much the same. If your field is a numeric, you shouldn't let anything other than a numeric be inserted.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38312248
Cluskitt is correct.  A numeric datatype can not hold a true Blank.  It is probably NULL and is being displayed as blank.  What are the symptoms you see as far as "messing up the database"?
0
 
LVL 15

Assisted Solution

by:David L. Hansen
David L. Hansen earned 1000 total points
ID: 38312260
You could also make the table's column not allow nulls at all.  This makes the VB code check unnecessary because the application will just throw an error if anything but a number is being inputted into that column.
0
 

Author Closing Comment

by:al4629740
ID: 38312285
Thank you for all your help!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

831 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