Using strpos or strrpos in a SELECT statement

I am trying to do the following SELECT statement:

SELECT first_c, substr(first_middle_name, strrpos(first_middle_name, " "), 1) as middle_c, first_middle_name, full_name FROM offenders LIMIT 0,50

It just fails. I need to extract the 1st character after the blank space in the first_middle_name field.

Thanks.

This is SQL, and you can not use PHP functions in your query to a database. If this is MySQL, you need to use these functions:

POSITION(substr IN str)

Returns the position of the first occurrence of substring substr in string str. Returns 0 if substr is not in str.

LOCATE(substr,str,pos)

Returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str.

SUBSTRING(str,pos,len)

Returns a substring len characters long from string str, starting at position pos.

RIGHT(str,len)

Returns the rightmost len characters from the string str.

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2, else it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used:

Then why does the substr function work in my SELECT statement?

Because MySQL have such function, and function have the same name as in PHP.
Here is a full list of MySQL string functions.

Sponsor our Newsletter | Privacy Policy | Terms of Service