. SQL . SQL Like

SQL Like

SQL Like Example One - Like with %

Q: What Godfather Movies have been made?

   
 Select Film_Title
   From Film_Table
  Where Film_Title  like "The Godfather%";

   
 The Godfather
 The Godfather Part II
 The Godfather Part III

Note: Using the % with Like is a "wildcard" for from none to any number of characters. The % can go anywhere in quotes, and you can even have more than one %.

Microsoft Access older version users please note: Some older versions of Microsoft Access use an * instead of a %, and a ? for it's _. The previous example would be as follows for older version MS Access Users:

 Select Film_Title
   From Film_Table
  Where Film_Title  like "The Godfather*";

SQL Like Example #2 - Like with _

Q: Is it Francis Ford Coppala or Francis Ford Coppola?

   
 Select Director_Last_Name
   From Director_Table
  Where Director_Last_Name  like "Copp_la";

   
 Coppola

Note: The _ is a wildcard for any single character. It can not represent a null. The _ can go anywhere in quotes, and you can even have more than one _. You can even mix _ and %.

Microsoft Access older version users please note:: Older versions of Microsoft Access use an * instead of a %, and a ? for it's _. The previous example would be as follows for older version MS Access Users:

 Select Director_Last_Name
   From Director_Table
  Where Director_Last_Name  like "Copp?la";

Special Thanks: to Thomas Hilmé² for pointing out that only older versions of Access use * and ?

Comments Comments are left by visitors to FluffyCat.com, are not endorsed by FluffyCat.com, and may or may not be accurate.

Comment by TheMadProfessor Rate this Comment

Regarding the questions about the 'links' field contaning multiple values delimited by semicolons, my suggestion is: forget it!

If at all possible, replace this field (which is probably making Codd spin in his grave) with a third table (a much easier to use and maintain normalized version):

Table AB_links
item 1: B_id
item 2: A_id

Now if you want to know what table A entries a given table B item is linked to:

select A_id from AB_links where B_id = "B id"

This could also be used as a subselect to extract various A-related info from table A, etc.

Comment by Larry Rate this Comment

If I understand the previous question correctly, this SQL with a like should do the trick:

Select *
From Emp_Table
Where Designation like '%president%'
or Designation like '%treasurer%'

If you know the exact string that a field will contain then you really shouldn't use a like, and should use =

Select *
From Emp_Table
Where Designation = 'president'
or Designation = 'treasurer'

Comment by usha Rate this Comment

hi everybody,
i have a problem in retriving the data from database using sql query can anybody help.
i have a emp table where 1 field is for designation.
i want only details of those who are either president/treasurer
so any body can suggest me the query using like command to get the details of presedient/treasurer

Comment by archive Rate this Comment

I have:
Table A:
GUID : system:string ' unique 30 character string
item 1 : system:string ' 7 characters
item 2 : system:string ' 5 characters
item 3 : system:string ' 15 characters'
item 4 : system:string ' 9 characters
item 5 : system:string ' 5 characters
item 700 : system :string : 10 characters

Table B:
GUID : system:string ' unique 28 character string
item 1 : system:string ' 7 characters
item 2 : system:string ' 5 characters
item 3 : system:string ' 15 characters'
item 6 : system:string ' 9 characters
item 7 : system:string ' 30 characters, unique GUIDs (1-30) from table A separated by a ";"
item 33700 : system :string : 10 characters

For example, a table b.GUID35.item7 has TABLE A.GUID23, TABLE A.GUID43,TABLE A.GUID46,TABLE A.GUID54,
a table b.GUID36.item7 has TABLE A.GUID13; TABLE A.GUID44;TABLE A.GUID56;TABLE A.GUID64;....20 GUIDs
a table b.GUID37.item7 has TABLE A.GUID323; TABLE A.GUID43;TABLE A.GUID46;TABLE A.GUID54;.....34 GUIDs
a table b.GUID5543.item7 has TABLE A.GUID487; TABLE A.GUID43; TABLE A.GUID46;TABLE A.GUID54; ....15 GUIDs
a table b.GUID4466.item7 has TABLE A.GUID529; TABLE A.GUID43;TABLE A.GUID46;TABLE A.GUID54; .....5 GUIDs

I want to query the two tables, and return for every GUIDxxx in Table B.item 7 the table A.item 2, Table A.item3, table A.item6.
I could use the tableb.item2.item3 as the criteria to return only a small number (maybe 50-200 for a specific combo) from the table A.

I hope this helps. I did not design this, and question why they choose to use system:string as the types for all entries, and use semi-colons to separate the entries. BUT I need to work with this. Sorry to be so vague about the names, etc but I must because of what it is...

THANKS for your help, I could not find any reference to semi-colon delimiters to store/retrieve data in a lot of books at the various bookstores, nor online!

Comment by Larry Rate this Comment

I don't see an example anywhere that has another field as part of a like, but if this works on your dbms it might be what you need.

select B.A_Links
from A, B
where B.A_Links like '%;A.Links'

If that doesn't, you might need to pass in the field from A in your program (if you are using Java, PHP, etc) or SQLPlus (if you are using Oracle), and it would look something like this:

select A_Links
from B
where A_Links like '%;$links_field'

Essentially, what you need are all rows in B where A_links ends with ';' and the Links value from A.

Comment by archive Rate this Comment

I did not design this. I have two tables, table A has a field "Link" that is of type string and contains a unique 30 character string (700 hundred of them). Table B contains a unique 28 character identifier, and a field that contains multiple 30 character unique identifiers (from table A) that are separated by a "semi-colon" in a field called A_Links. How do I select all from table B that contains a unique identifier from table A? As a separate record? Table B will have maybe 10-15 of the unique identifiers from table A.

to add your own comment