Wildcards in sql

Question

How does wildcards works in sql. If I do select * from table it give all the fields. But if I do select a* from table it gives error. Shouldn't it give all fields which begins with a? I am little confused.

Answer 1

SELECT * FROM tableName literally means "select all columns from tableName".

Philip Graham is right about his answer where he asked to use a.*

Wildcards help you search for strings about which you are not sure. These are almost always used with the LIKE keyword and put in WHERE clauses or searched CASE statements.

There are two wildcard characters - % and _.

% is used to find any string of 0 or more length. E.g.,

SELECT firstName
  FROM persons
 WHERE UPPER(firstName) LIKE 'J%'

This will return all the firstName from the persons table where firstname starts with letter J. This would return "Jason", "James", "Josh", "Jessica" and much more.

Note that UPPER function was used to eliminate case sensitivity.

Next, you can have an _ character that looks for the presence of one single character.

SELECT firstName
  FROM persons
 WHERE UPPER(firstName) LIKE 'J_M__'

This would return "James", "Jimmy", "Jamos", "Jxmx" and filter away any "Jason", "Jaguar", etc.

For more info click here

Answer 2

You can use a.* where a is the name of the table. For instance in

select a.* from a left join b on a.id = b.id 

You would return only the fields from a but not from b