Calculated Value for Primary Key
I have a problem.
I have a Client table (client_id, client_name, address).
I want the client_id is automatically assigned when a new client is registered based on following criteria:
Format: X-00# where X is the first letter of client name and
# is the (total +1) number of clients with the same first letter as X.
My first client is John Doe.
So the client_id for John Doe is J-001
I already have 3 clients (Jennifer, Dave and Jackson)
So, John Doe's client_id is J-003
How do I code this logic? Thanks.