How to obtain the Number of chrs left of a specifed chr using an sql statement.

How to optain the Number of chrs left of a specifed chr using an sql statement.

I want to find all the lastnames that have five Chrs..and not use lengthy instr functions to optain the same result.

Objective 1: to find the position of the chr you are looking for. this can be any chr in a string but only works on the first Chr found.

objective 2 , to find the #chrs left of the search chr

Meaning of this code:
Table Fieldname = name
data lastname,firstname

Sql,james
jones,tom
long,how
craig,jenny
howard,mike
masters,shawn
smith,john

Sample SQL Statement
Sql statement used to optain the results
sSql = "SELECT * FROM data WHERE PATINDEX('%,%', name) -1 = 5"

The Sql function used is called PATINDEX
The sql statement used searched for the first , in the string

PATINDEX returns that the , is the 6th chr the - 1 moves back to the number of chrs found.
the result returns
jones,tom
craig,jenny
smith,john

This code actually does work if anyone enhances on this please let me know i would like to see other ways of getting the results to the right of the ,
again using an sql statement as above.

Thanks,
hostmaster@wantednow.com
www.wantednow.com