I'm trying to fetch all instances where the 1st letter of a person's first name is equal to P.
This is what I came up with, which doesn't return anything:
$sql="SELECT * FROM people WHERE SUBSTRING(FirstName,0,1) = 'P'";
Suggestions?
-
Weird, I'd have thought that would work.alex– alex2011-03-18 04:43:13 +00:00Commented Mar 18, 2011 at 4:43
-
13Substring indexes are 1-originJim Garrison– Jim Garrison2011-03-18 04:44:22 +00:00Commented Mar 18, 2011 at 4:44
-
@Alex Same here. I'm not entirely sure why it doesn't. The LIKE method works though, but from what I've read, it might be less efficient than using SUBSTRING.Ian– Ian2011-03-18 04:45:32 +00:00Commented Mar 18, 2011 at 4:45
-
@Jim Thanks, I've since fixed my previous answer :)alex– alex2011-03-18 04:45:45 +00:00Commented Mar 18, 2011 at 4:45
Add a comment
|
1 Answer
The reason your expression doesn't work is that substring() positions are 1-based
Try either of these:
where FirstName like 'P%'
or
where substring(FirstName,1,1) = 'P'
4 Comments
Ian
Sweet. I was afraid that would return all instances of the character p, and not just when it's the first character.
Jim Garrison
@Ian that would be
like '%P%'Jim Garrison
@alex 1-origin was more common in the past. 0-origin became the norm with pointer-based arithmetic when it made sense to express indexes as offsets from a base address. When SQL was originally designed ('70s-'80s) 1-origin and 0-origin were equally prevalent.
Tural Asgarov
"substring() positions are 1-based" that sentence saved me a ton. Thanks!