SQL .

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 and may or may not be accurate.
Comment by TheMadProfessor on 2007-10-16 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 on 2007-03-03 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 on 2007-02-28 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 on 2006-06-05 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 on 2006-06-05 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 on 2006-06-05 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.

 
Sign in to comment on SQL Like.