Wildcard?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pucker22
Forum Newbie
Posts: 1
Joined: Sat Mar 08, 2008 12:12 am

Wildcard?

Post by pucker22 »

Hello all,

I'm currently in need for some help, basically I would like to show all information on accounts that starts with the letter A

for example mysql has a table named names which has all sorts of names, some starting with A,B,C,D etc...

I want to be able to get all information on only the user's that start with the letter A

Would I use wildcard (*) to do this? Could you possible give me a brief example?

Also I had another question,

We use Cpanel which has mysql built in, we would like to connect to our mysql from another server, what would we be changing localhost to? would it be the domain:30083 ? (bare in mind we are using cpanel)

Thanks in advance any comments is much appericated!

=)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Wildcard?

Post by onion2k »

To do a wildcard search in MySQL use LIKE with a % for the wildcard. Eg, match everything starting with 'A':

Code: Select all

SELECT `tablename`.`fieldname`
FROM `tablename`
WHERE 1 
AND `tablename`.`fieldname` LIKE 'A%'
LIKE is case insensitive so that will match everything starting with either 'a' or 'A'. If you need it to be case sensitive you need to use a case sensitive collation when the table is defined, or force the collation to a different type in the query, eg:

Code: Select all

SELECT `tablename`.`fieldname`
FROM `tablename`
WHERE 1 
AND `tablename`.`fieldname` COLLATE latin1_bin LIKE 'A%'
You can put % anywhere in the search. To match strings with a particular substring anywhere in them you could use

Code: Select all

SELECT `tablename`.`fieldname`
FROM `tablename`
WHERE 1 
AND `tablename`.`fieldname` COLLATE latin1_bin LIKE '%ing%'
That will match 'king', 'ingot', and 'things'.

Similarly you can use % in the middle of a search:

Code: Select all

SELECT `tablename`.`fieldname`
FROM `tablename`
WHERE 1 
AND `tablename`.`fieldname` COLLATE latin1_bin LIKE 'A%m'
That will match 'Adam' for example.

If you need to match strings of a specific length you should use _ rather than %. Eg, to match a string that starts with A and it 4 characters long you'd use:

Code: Select all

SELECT `tablename`.`fieldname`
FROM `tablename`
WHERE 1 
AND `tablename`.`fieldname` LIKE 'A___'
If you need more powerful searching you should use REGEXP or RLIKE to search for strings that match a regular expression. Be careful with that though, they can get very slow if you're searching a large data set.
Post Reply