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!
=)
Wildcard?
Moderator: General Moderators
Re: Wildcard?
To do a wildcard search in MySQL use LIKE with a % for the wildcard. Eg, match everything starting with '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:
You can put % anywhere in the search. To match strings with a particular substring anywhere in them you could use
That will match 'king', 'ingot', and 'things'.
Similarly you can use % in the middle of a search:
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:
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.
Code: Select all
SELECT `tablename`.`fieldname`
FROM `tablename`
WHERE 1
AND `tablename`.`fieldname` LIKE 'A%'Code: Select all
SELECT `tablename`.`fieldname`
FROM `tablename`
WHERE 1
AND `tablename`.`fieldname` COLLATE latin1_bin LIKE 'A%'Code: Select all
SELECT `tablename`.`fieldname`
FROM `tablename`
WHERE 1
AND `tablename`.`fieldname` COLLATE latin1_bin LIKE '%ing%'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'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___'