I am trying to program a routine to check for certain elements within an email address. i.e. *@mailserver.com The problem is that I can't see a way to have sql check for wildcards. Actually at this moment I am using Access to program my database. I am using some sql in my programming. Is there a way to do this? Or do I need to migrate over to SQL Server to do it? Can it even be done?
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 wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Wildcards in SQL
- Thread starter Rev
- Start date
I don't know about Access, but in VFP you can imbed VFP functions in SQL - which isn't a good idea if you're planning to upsize to SQL server, and there is a performance hit. An example:
SELECT * FROM names WHERE '@mailserver.com' $(lower(email))
The VFP $ operator means "is contained in".
SELECT * FROM names WHERE '@mailserver.com' $(lower(email))
The VFP $ operator means "is contained in".
captaincaveman
Programmer
I think what you're looking for is:
Select * from names where mail like '%@mailserver.com'
The % is a wildcard operator in SQL.
Select * from names where mail like '%@mailserver.com'
The % is a wildcard operator in SQL.
Captaincaveman has the right of it. The original SQL standard specifies % (percent) as the multi-character wildcard symbol and _ (underscore) as the singl-character wildcard symbol. However, MS Access (and several other database systems) use the DOS wildcard symbols, * form multi-character and ? for single-character. I've even run across one odd-ball DBMS that allowed both!
using the 'LIKE %String%'in the Where clause will return true if the 'String' exist in the row being examined, regardless of the position of the 'String' in the row.<br>
For example:<br>
<br>
Select * from myTable WHERE ColumnX LIKE %ABCD%<br>
<br>
will return true if 'ABCD' was found in ColumnX.
For example:<br>
<br>
Select * from myTable WHERE ColumnX LIKE %ABCD%<br>
<br>
will return true if 'ABCD' was found in ColumnX.
- Status