By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,400 Members | 1,315 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,400 IT Pros & Developers. It's quick & easy.

Find if position 10 trhough 15 are blank in a table

100+
P: 468
This is probably a dumb question but I'm having a hard time figuring it out.
I have a field in a table that has a number in the first 10 positions the rest of the field is blank and I want to delete that record from the table. What's the best way.
Thanks
4 Weeks Ago #1

✓ answered by isladogs

Create a delete query similar to this using table & field names as appropriate:

Expand|Select|Wrap|Line Numbers
  1. DELETE YourTableName.*
  2. FROM YourTableName
  3. WHERE YourTableName.YourFieldName Is Null;
  4.  

Share this Question
Share on Google+
9 Replies

isladogs
Expert
P: 54
Create a delete query similar to this using table & field names as appropriate:

Expand|Select|Wrap|Line Numbers
  1. DELETE YourTableName.*
  2. FROM YourTableName
  3. WHERE YourTableName.YourFieldName Is Null;
  4.  
4 Weeks Ago #2

ADezii
Expert 5K+
P: 8,750
I may be reading this incorrectly, but the Record to be Deleted has Numbers in positions 1 to 10 and is Blank starting from position 11. Maybe I am just getting old (LOL).
4 Weeks Ago #3

isladogs
Expert
P: 54
I'm hoping you are reading it incorrectly.
In any case if a field value has 10 numbers and character 11 onwards are null, the field is 10 characters long!

However, if you are correct, my delete query would be completely wrong!
4 Weeks Ago #4

ADezii
Expert 5K+
P: 8,750
I was thinking along the lines that the following Record would not be Deleted. Positions 1 to 10 in the Field are Numeric, but Position 11 on is occupied.
Expand|Select|Wrap|Line Numbers
  1. [Field1]
  2. 7777777777ABC
Wheras, the following Record would be Deleted since the Field in question has numeric values for the first 10 Positions, but Position 11 is not occupied.
Expand|Select|Wrap|Line Numbers
  1. [Field1]
  2. 9876523507
I would say go with your instinct, it is probably keener than mine (LOL).
4 Weeks Ago #5

isladogs
Expert
P: 54
I assumed the OP meant that the first 10 records had numbers in that field and subsequent records had null values for the field

I guess we'll have to wait and see if the OP returns with the 'correct' meaning.
4 Weeks Ago #6

ADezii
Expert 5K+
P: 8,750
In hindsight, my interpretation does seem a little off!
4 Weeks Ago #7

100+
P: 468
Sorry for the confusion. The field will have numbers in the first 5 positions (12345 blank) there are other fields in the table that have numbers in the first 5 positions and then characters (12345 abcdef)
I only want to delete those records with numbers and then blanks.
I hope this is a little clearer.
4 Weeks Ago #8

isladogs
Expert
P: 54
First prize goes to adezii!!!!

OK - don't use the DELETE query I gave you earlier!
However, your new description doesn't match the previous one.
Please can you supply several example values and state what you want to happen in each case
4 Weeks Ago #9

100+
P: 200
According to post # 8
Expand|Select|Wrap|Line Numbers
  1. WHERE LEN(RTRIM(Your_Field_Name)) <= Numeric_digits
4 Weeks Ago #10

Post your reply

Sign in to post your reply or Sign up for a free account.