|
|
| Article |
fooclass
Junior Member
Articles: 4 Comments: 1
|
|
|
| Posted: Wed Jul 11, 2007 1:04 pm |
|
|
LIKE statement can be used in query if application needs to match certain values in a database field. Here I’m going to show you few examples about different wildcards used in LIKE statement.
Wildcard % will match any number of characters. But in case if we need to match a single character, use wildcard "_". So if we need to get all the employees names such as Jamy, Jame, Jas etc... use a query similar to this.
| Code: | | SELECT * FROM Employee WHERE NAME Like 'Jam_' |
If we need to retrieve all employees names starting 'Peter' in Employee table, following query can be used.
| Code: | | SELECT * FROM Employee WHERE NAME Like 'Peter%' |
Now we may need the names ending with 'Peter' and the query should look like
| Code: | | SELECT * FROM Employee WHERE NAME Like '%Peter' |
In some database applications, it may require to show A to Z index of some field data. Following query will pull all the names starting with letter "a" from table Employee
| Code: | | SELECT * FROM Employee WHERE NAME Like 'a%' |
I have seen developers using MID function to achieve the above query results but LIKE statement should give a better performance so try to use it whenever possible.
Using Regular expression with LIKE statement
In some queries it may require to use regular expressions with LIKE statement. For example if we need to get all the valid text data from a field, you can use something like this
| Code: | | SELECT * FROM Employee WHERE NAME Like '[A-Za-z]' |
In case users have entered some number data in database, try this to find out that
| Code: | | SELECT * FROM Employee WHERE NAME Like '[0-9]' |
I have used following query to find all the unwanted characters in a field such as hidden character etc...
| Code: | | SELECT * FROM Employee WHERE NAME Like '[^A-Za-z0-9_.]' |
This will pull all the rows which are not characters or numbers. This is handy query if a DB admin wants to clean up some of the data which is entered by the internet users. Some internet users enter -- or dots (.) to submit fake records in database.
Please post your comments and suggestions about this article here. I'll try to answer to your queries.
Happy coding!! |
|
|
| Comments |
Daniel
Contributing Member
Joined: 14 Feb 2007
Articles: 0
Comments: 2
|
| Posted: Wed Jul 11, 2007 2:59 pm Post subject: |
|
|
| I have learned couple of new tips from your article. Thanks for this. |
|
|
| |
Page 1 of 1 |
All times are GMT
You cannot post articles in this chapter
You cannot edit your articles in this chapter
You cannot delete your articles in this chapter
You cannot rate articles in this chapter
You cannot post comments in this chapter
You cannot edit your comments in this chapter
You cannot delete your comments in this chapter
You cannot rate comments in this chapter
|
|
