Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Wildcards or tokens in Select SQL 2
- Thread starter kelunaboy
- Start date
- Thread starter
- #2
Typically, the $ command requires the data to be the same length.
Example.
x = "010020030040"
* fieldname is CHAR(3)
Select fieldname where myfield $ "040" works because it is fixed length and matches a 3 character span.
In this case 010 020 030 040
A better solution may be the LIKE command.
Example:
SELECT * FROM mytable WHERE mytable.company LIKE "040"
Play with both and see what fits your needs.
Jim Osieczonek
Delta Business Group, LLC
Example.
x = "010020030040"
* fieldname is CHAR(3)
Select fieldname where myfield $ "040" works because it is fixed length and matches a 3 character span.
In this case 010 020 030 040
A better solution may be the LIKE command.
Example:
SELECT * FROM mytable WHERE mytable.company LIKE "040"
Play with both and see what fits your needs.
Jim Osieczonek
Delta Business Group, LLC
The more I think about it - ATC() or OCCCURS() may be a better fit. Again, it really depends on what you're trying to seach for.
Jim Osieczonek
Delta Business Group, LLC
Jim Osieczonek
Delta Business Group, LLC
- Thread starter
- #5
Aloha jimoo,
I started with the LIKE operator but must be doing something wrong since I get no hits using it without padding it with underscores.
the app i'm developing (a conversion from MS access) is a boat registration data base where the vessel reg numbers might appear as "HA 1234 CF", or "HA 6500 E ". char(10) with a space in the data.
LIKE only works for me when I pad with the underscore.
Select * from myfile where myfield LIKE "________CF" = works to return all records that end with "CF".
Select * from myfile where myfield LIKE "CF" = does not work and finds zero records.
Select * from myfile where "CF" $ myfield = works to return all records that end with "CF".
I want to allow wildcard searches but have not decided the best way to approach this as yet. My plan is to allow the user to enter '?' or '*' parse the entry, and build the Select. Right now the $ Operator seems the easier of the two. I understand it's not Rushmore optimized, but I only have 50,000 records - so it seems quick enough.
Your input is appreciated - thanks again
alan
I started with the LIKE operator but must be doing something wrong since I get no hits using it without padding it with underscores.
the app i'm developing (a conversion from MS access) is a boat registration data base where the vessel reg numbers might appear as "HA 1234 CF", or "HA 6500 E ". char(10) with a space in the data.
LIKE only works for me when I pad with the underscore.
Select * from myfile where myfield LIKE "________CF" = works to return all records that end with "CF".
Select * from myfile where myfield LIKE "CF" = does not work and finds zero records.
Select * from myfile where "CF" $ myfield = works to return all records that end with "CF".
I want to allow wildcard searches but have not decided the best way to approach this as yet. My plan is to allow the user to enter '?' or '*' parse the entry, and build the Select. Right now the $ Operator seems the easier of the two. I understand it's not Rushmore optimized, but I only have 50,000 records - so it seems quick enough.
Your input is appreciated - thanks again
alan
-
2
- #6
Select * from myfile where myfield LIKE "CF" = does not work and finds zero records.
Change the above statement to :
Select * from myfile where myfield LIKE "%CF"
- will fetch the records. You need to use % to suggest any character(s) before "CF". If "CF" was somewhere in the middle of the string you could use "%CF%". You can also use a combination of "_" and "%" depending on your search requirements.
HTH
- Thread starter
- #7
Thanks for the stars.
However it is not an undocumented feature. Both "%" and "_" are documented SQL select wildcard characters and mentioned in the online help on SELECT - SQL command. "%" represents a sequence of unknown characters and "_" represents a single unknown character in a string.
However it is not an undocumented feature. Both "%" and "_" are documented SQL select wildcard characters and mentioned in the online help on SELECT - SQL command. "%" represents a sequence of unknown characters and "_" represents a single unknown character in a string.
- Status
Similar threads
- Locked
- Question