Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query to select all based on first char of a field

Status
Not open for further replies.

justride

Programmer
Jan 9, 2004
251
US
Hi, I am attempting to query a table of 3 fields, I am trying to select name,definition from table terms where the first letter in name is = to a character i specify, a php variable if you will.

I have tried:
$sql3 = "SELECT *, LEFT(name,1) as name1
FROM terms
WHERE name1 = '$char'
ORDER BY name1";
which generates the mysql error of cannot execute.

I have also tried:
$sq3 = "SELECT name, definition
FROM terms
WHERE SUBSTRING(name,1) = '$char' ORDER BY name";

which doesnt seem to yield any results.

Any suggestions? thanks much!
 
or
$i='a';

"SELECT * from terms
where name like '$i%'
order by name"

where a is any letter you like
 
Hi guys, i tried both solutions and they yielded empty sets. here is my table, an example table..
+----+-------------+----------------------------------------
| ID | NAME | DEFINITION|
+----+-------------+----------------------------------------
| 1 | Acre | Aland equal to 43,560 square feet
| 2 | Bankruptcy | The financial inability to pay one's
| 3 | Appraisal | An Opinion or an Estimate of the value
+----+-------------+----------------------------------------
3 rows in set (0.01 sec)
 
Hey guys,

select * from terms
where name like '$char%'
order by name;

this works

i had the the syntax wrong the fist time, thanks so much!!!
 
Are you changing to lower case to avoid the case sensitivity?

$i = 'a'

Select * from terms where Lower(NAME) like '$i%'

or

select * from terms where LOWER(LEFT(NAME, 1)) = '$i'

 
Hi justride809
For ur part1 of problem which generates the mysql error of cannot execute. refer to thread436-740809
ur query 2
$sq3 = "SELECT name, definition
FROM terms
WHERE SUBSTRING(name,1) = '$char' ORDER BY name";

would work with SUBSTRING(name,1,1). In ur case the substring was from letter 1 till the end of the text. So with 'justride809' as name fld u selected the entrie namw and tried to math with j only

With this sleipnir214 solution will also work . For case sensitivity check the binary option. In MySql, SQL LIKE comparison is not case sensistive bur REGEXP is.
So u can have any way to work u quey and then select the best method



[ponder]
----------------
ur feedback is a very welcome desire
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top