Using Substring in MySQL criteria

Question

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?

@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, Mar 18 '11 at 04:45
@Jim Thanks, I've since fixed my [previous answer](http://stackoverflow.com/questions/5347111/a-z-post-query-using-ajax-in-wordpress/5347158#5347158) :) — alex, Mar 18 '11 at 04:45

Jim Garrison · Accepted Answer · 2013-07-02T15:58:24.760

48

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'

edited Jul 02 '13 at 15:58

answered Mar 18 '11 at 04:39

Jim Garrison

1

Sweet. I was afraid that would return all instances of the character p, and not just when it's the first character. – Ian Mar 18 '11 at 04:43
@Ian that would be `like '%P%'` – Jim Garrison Mar 18 '11 at 04:44
12

@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. – Jim Garrison Mar 18 '11 at 04:53
1

Thanks for that (I must be young) :) – alex Mar 18 '11 at 04:59
"substring() positions are 1-based" that sentence saved me a ton. Thanks! – TuralAsgar Apr 13 '20 at 22:21

1 Answers1