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

Like Statement

P: 73
I am trying to run a query on numbers that start with the number 9. There are some numbers that are 10 characters long and some that are 7 and I want to pull in all of them. I ran the query using like "9*" however it didn't have any results when I know there is. I went to the table and tried change that Field to a number however it is giving me an error message exceeds size limits to change to number from text. My table is 158,000 rows. Is there any way of converting it to a number or a way to run the like statement on a text field? Any suggestions?

Also have a second stipulation added to this, but more concerned with the first statement. Is there a way that I could also pull in every number that started with a 9 and it only pulled in those that had the 4th number in start with a 7 or 8. ie 9007 or 9008.

Thanks for your help
Jan 10 '08 #1
Share this Question
Share on Google+
7 Replies


puppydogbuddy
Expert 100+
P: 1,923
I am trying to run a query on numbers that start with the number 9. There are some numbers that are 10 characters long and some that are 7 and I want to pull in all of them. I ran the query using like "9*" however it didn't have any results when I know there is. I went to the table and tried change that Field to a number however it is giving me an error message exceeds size limits to change to number from text. My table is 158,000 rows. Is there any way of converting it to a number or a way to run the like statement on a text field? Any suggestions?

Also have a second stipulation added to this, but more concerned with the first statement. Is there a way that I could also pull in every number that started with a 9 and it only pulled in those that had the 4th number in start with a 7 or 8. ie 9007 or 9008.

Thanks for your help
Try this:
Like "[9]123"
Like "[9]*"
Like "[90]*"
Like "[!90]*"

The brackets designate the characters inside the brackets as the wildcard characters

The * designates any character following the bracketed character as a wildcard

The exclamation point inside the brackets stands for Not in the list. The query results will include all entries whose characters do not match any character in the list within the brackets.
Jan 10 '08 #2

JKing
Expert 100+
P: 1,206
Hi,

Keeping the number as a text field should be just fine.

Try forming your sql statement as follows:

Expand|Select|Wrap|Line Numbers
  1. SELECT yourField
  2. FROM yourTable
  3. WHERE yourField LIKE '9##8*'
  4. OR yourField LIKE '9##7*'
  5.  
yourTable - The table you are looking up
yourField - The field you are looking up within yourTable
Jan 10 '08 #3

P: 73
Try this:
Like "[9]123"
Like "[9]*"
Like "[90]*"
Like "[!90]*"

The brackets designate the characters inside the brackets as the wildcard characters

The * designates any character following the bracketed character as a wildcard

The exclamation point inside the brackets stands for Not in the list. The query results will include all entries whose characters do not match any character in the list within the brackets.
I still couldn't get this to work. I imported the file from a text file because it was 160,000 rows and set up the delimited columns. Would that make the difference on why I couldn't get it to work. It gives me no results. I went thru the table by hand and know there are more than a few hundred results
Jan 10 '08 #4

puppydogbuddy
Expert 100+
P: 1,923
I still couldn't get this to work. I imported the file from a text file because it was 160,000 rows and set up the delimited columns. Would that make the difference on why I couldn't get it to work. It gives me no results. I went thru the table by hand and know there are more than a few hundred results
1. Do a query on your table >>>>Select * From Your Table
2. In design view, place the wildcard search in the criteria row of the field you want to search.
3. Run your query
Jan 10 '08 #5

FishVal
Expert 2.5K+
P: 2,653
Hi, there.

Just a guess.
SQL syntax may be set to ANSI 92. Try to use "%" wildcard instead of "*".

Regards,
Fish
Jan 10 '08 #6

puppydogbuddy
Expert 100+
P: 1,923
Hi, there.

Just a guess.
SQL syntax may be set to ANSI 92. Try to use "%" wildcard instead of "*".

Regards,
Fish
Hi Fish,

Good thinking!!

pDog
Jan 10 '08 #7

P: 73
Hi, there.

Just a guess.
SQL syntax may be set to ANSI 92. Try to use "%" wildcard instead of "*".

Regards,
Fish


I still couldn't get it to work properly that way. However I ended up trying to import it again and when I was setting up my delimited option there was an Advanced button and I clicked on it and it let me change that column to a number column and was able to run the like statement now and works.

Thanks for assistance.
Jan 11 '08 #8

Post your reply

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