469,908 Members | 2,219 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,908 developers. It's quick & easy.

Find if position 10 trhough 15 are blank in a table

470 256MB
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
Oct 25 '20 #1
10 4062
isladogs
315 Expert Mod 256MB
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.  
Oct 25 '20 #2
ADezii
8,800 Expert 8TB
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).
Oct 26 '20 #3
isladogs
315 Expert Mod 256MB
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!
Oct 26 '20 #4
ADezii
8,800 Expert 8TB
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).
Oct 26 '20 #5
isladogs
315 Expert Mod 256MB
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.
Oct 26 '20 #6
ADezii
8,800 Expert 8TB
In hindsight, my interpretation does seem a little off!
Oct 26 '20 #7
CD Tom
470 256MB
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.
Oct 26 '20 #8
isladogs
315 Expert Mod 256MB
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
Oct 27 '20 #9
SioSio
266 256MB
According to post # 8
Expand|Select|Wrap|Line Numbers
  1. WHERE LEN(RTRIM(Your_Field_Name)) <= Numeric_digits
Oct 27 '20 #10
NeoPa
32,231 Expert Mod 16PB
I've reset Best Answer as it was a good answer in itself - but to a different version of the question.

@CD Tom.
Please try to provide clear questions - with example data being very important if you struggle to express your meaning clearly in words.

PS. My guess, piecing together various of the comments, is that when CD Tom refers to Fields within his table what he actually means is values within different records but of the same Field.
Dec 20 '20 #11

Post your reply

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

Similar topics

6 posts views Thread by Scott Sabo | last post: by
reply views Thread by deepak kumar | last post: by
2 posts views Thread by vunet.us | last post: by
4 posts views Thread by louvino | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.