A really stupid problem with SQL
-
Well , I wrote a little app in VB that queries a table. I used DAO.I created a SQL generator that builds up a statement. But somehow only if I use * instead of % it works. For example , if i execute
select * from Phonebook where Firstname like 'M*'
I will get the appropriate records beginning with M. but if i write:
select * from Phonebook where Firstname like 'M%'
than all i get is an empty recordset! Why is that?! i remember that i used % before and it worked just fine. maybe someone knows what is the problem? thx!
-
% is standard SQL, however Access (and thus DAO) uses *. I got bit by the opposite problem when I made the transition from DAO to ADO/SQL Server James Sonork: Hasaki "I left there in the morning with their God tucked underneath my arm their half-assed smiles and the book of rules. So I asked this God a question and by way of firm reply, He said - I'm not the kind you have to wind up on Sundays." "Wind Up" from Aqualung, Jethro Tull 1971
Ahh thanks. And what about ADO ? Does it work with % or must use * too ?
-
Well , I wrote a little app in VB that queries a table. I used DAO.I created a SQL generator that builds up a statement. But somehow only if I use * instead of % it works. For example , if i execute
select * from Phonebook where Firstname like 'M*'
I will get the appropriate records beginning with M. but if i write:
select * from Phonebook where Firstname like 'M%'
than all i get is an empty recordset! Why is that?! i remember that i used % before and it worked just fine. maybe someone knows what is the problem? thx!
% is standard SQL, however Access (and thus DAO) uses *. I got bit by the opposite problem when I made the transition from DAO to ADO/SQL Server James Sonork: Hasaki "I left there in the morning with their God tucked underneath my arm their half-assed smiles and the book of rules. So I asked this God a question and by way of firm reply, He said - I'm not the kind you have to wind up on Sundays." "Wind Up" from Aqualung, Jethro Tull 1971
-
Ahh thanks. And what about ADO ? Does it work with % or must use * too ?
That I'm not sure about, I think it uses * for Access and % for others, but I'm not sure as I haven't done any ADO on Access. Oddly that was one combination I was able to avoid. James Sonork: Hasaki "I left there in the morning with their God tucked underneath my arm their half-assed smiles and the book of rules. So I asked this God a question and by way of firm reply, He said - I'm not the kind you have to wind up on Sundays." "Wind Up" from Aqualung, Jethro Tull 1971
-
That I'm not sure about, I think it uses * for Access and % for others, but I'm not sure as I haven't done any ADO on Access. Oddly that was one combination I was able to avoid. James Sonork: Hasaki "I left there in the morning with their God tucked underneath my arm their half-assed smiles and the book of rules. So I asked this God a question and by way of firm reply, He said - I'm not the kind you have to wind up on Sundays." "Wind Up" from Aqualung, Jethro Tull 1971
James T. Johnson wrote: but I'm not sure as I haven't done any ADO on Access I used it.You have to use % .:) Mazy "The path you tread is narrow and the drop is shear and very high, The ravens all are watching from a vantage point near by, Apprehension creeping like a choo-train uo your spine, Will the tightrope reach the end;will the final cuplet rhyme?"Cymbaline-Pink Floyd
-
Ahh thanks. And what about ADO ? Does it work with % or must use * too ?
Dan Pomerchik wrote: And what about ADO ? Does it work with % or must use * too ? In ADO You have to use % .:) Mazy "The path you tread is narrow and the drop is shear and very high, The ravens all are watching from a vantage point near by, Apprehension creeping like a choo-train uo your spine, Will the tightrope reach the end;will the final cuplet rhyme?"Cymbaline-Pink Floyd
-
Dan Pomerchik wrote: And what about ADO ? Does it work with % or must use * too ? In ADO You have to use % .:) Mazy "The path you tread is narrow and the drop is shear and very high, The ravens all are watching from a vantage point near by, Apprehension creeping like a choo-train uo your spine, Will the tightrope reach the end;will the final cuplet rhyme?"Cymbaline-Pink Floyd
Oh this is crap, every time you have to use a different character :omg: Why can't they just SET a STANDARD and USE it in all db's/platforms/etc.!? Anyway thanks guys, good to have some people who help :-)
-
Oh this is crap, every time you have to use a different character :omg: Why can't they just SET a STANDARD and USE it in all db's/platforms/etc.!? Anyway thanks guys, good to have some people who help :-)
-
Oh this is crap, every time you have to use a different character :omg: Why can't they just SET a STANDARD and USE it in all db's/platforms/etc.!? Anyway thanks guys, good to have some people who help :-)
Dan Pomerchik wrote: Why can't they just SET a STANDARD and USE it in all db's/platforms/etc.!? How do you think they keep us on our feet? :) Don't you hate it when you forget to log on with the message boards? Nick Parker
-
Oh this is crap, every time you have to use a different character :omg: Why can't they just SET a STANDARD and USE it in all db's/platforms/etc.!? Anyway thanks guys, good to have some people who help :-)
There is a standard, %, I don't know if its in writing but everything I've seen except Access uses it :) Access (and DAO) is used on windows only, so it made sense to use * as the 'match anything wildcard' which is what windows uses. James Sonork: Hasaki "I left there in the morning with their God tucked underneath my arm their half-assed smiles and the book of rules. So I asked this God a question and by way of firm reply, He said - I'm not the kind you have to wind up on Sundays." "Wind Up" from Aqualung, Jethro Tull 1971
-
Well , I wrote a little app in VB that queries a table. I used DAO.I created a SQL generator that builds up a statement. But somehow only if I use * instead of % it works. For example , if i execute
select * from Phonebook where Firstname like 'M*'
I will get the appropriate records beginning with M. but if i write:
select * from Phonebook where Firstname like 'M%'
than all i get is an empty recordset! Why is that?! i remember that i used % before and it worked just fine. maybe someone knows what is the problem? thx!
Because * replace the rest of characters and % replace only One Character.... For Example if you have a table with the following records Mother My Mine M* get all the records and M% only the Second Record... Best Regards Carlos Antollini. Sonork ID 100.10529 cantollini
-
Because * replace the rest of characters and % replace only One Character.... For Example if you have a table with the following records Mother My Mine M* get all the records and M% only the Second Record... Best Regards Carlos Antollini. Sonork ID 100.10529 cantollini
:eek: :eek: Carlos Antollini wrote: M% only the Second Record... No,Carlos.Thats not true.'%' get all of them too.:) Mazy "The path you tread is narrow and the drop is shear and very high, The ravens all are watching from a vantage point near by, Apprehension creeping like a choo-train uo your spine, Will the tightrope reach the end;will the final cuplet rhyme?"Cymbaline-Pink Floyd
-
:eek: :eek: Carlos Antollini wrote: M% only the Second Record... No,Carlos.Thats not true.'%' get all of them too.:) Mazy "The path you tread is narrow and the drop is shear and very high, The ravens all are watching from a vantage point near by, Apprehension creeping like a choo-train uo your spine, Will the tightrope reach the end;will the final cuplet rhyme?"Cymbaline-Pink Floyd
Mazy It's True... :-O I found the following: Kind of match Pattern Match (returns True) No match (returns False) Multiple characters a*a aa, aBa, aBBBa *ab* abc, AABB, Xab aZb, bac Special character a[*]a a*a aaa Multiple characters ab* abcdefg, abc cab, aab Single character a?a aaa, a3a, aBa aBBBa Single digit a#a a0a, a1a, a2a aaa, a10a Range of characters [a-z] f, p, j 2, & Outside a range [!a-z] 9, &, % b, a Not a digit [!0-9] A, a, &, ~ 0, 1, 9 Combined a[!b-m]# An9, az0, a99 abc, aj0 Best Regards....;) Carlos Antollini. Sonork ID 100.10529 cantollini
-
Because * replace the rest of characters and % replace only One Character.... For Example if you have a table with the following records Mother My Mine M* get all the records and M% only the Second Record... Best Regards Carlos Antollini. Sonork ID 100.10529 cantollini
In T-SQL (SQL Server) the % character will do a wildcard beyond that point(all characters)
'boo%' results in book, books, boobs....
the _ character will act as a single wildcard
'b_ok' will find anything for the second character(i.e. - book). Nick Parker
-
Because * replace the rest of characters and % replace only One Character.... For Example if you have a table with the following records Mother My Mine M* get all the records and M% only the Second Record... Best Regards Carlos Antollini. Sonork ID 100.10529 cantollini
Oh thanks a lot , that explains everything :-) :-) - Dan "Intel inside - Idiot outside"
-
Dan Pomerchik wrote: Why can't they just SET a STANDARD and USE it in all db's/platforms/etc.!? How do you think they keep us on our feet? :) Don't you hate it when you forget to log on with the message boards? Nick Parker
Nick Parker wrote: How do you think they keep us on our feet? :-) Nick Parker wrote: Don't you hate it when you forget to log on with the message boards? Actually, I'm allowing those cookies so I don't have to log on :-) Btw, I tried to change the default password I got and somehow it won't change. Does anyone knows something about it ? - Dan "Intel inside - Idiot outside"
-
Well , I wrote a little app in VB that queries a table. I used DAO.I created a SQL generator that builds up a statement. But somehow only if I use * instead of % it works. For example , if i execute
select * from Phonebook where Firstname like 'M*'
I will get the appropriate records beginning with M. but if i write:
select * from Phonebook where Firstname like 'M%'
than all i get is an empty recordset! Why is that?! i remember that i used % before and it worked just fine. maybe someone knows what is the problem? thx!
Dammit! I'm totaly confused now! Someone said that % is for one char and * for all. Someone else said % is standard and * for DAO/ADO. What happening!? :-) - Dan "Intel inside - Idiot outside"