I have a column "employee_Id" in my Table "Employee".
employee_Id is having employee name and date of birth. For example :
Jason-21996
and Buttler
Please help me write a select query which returns Jason
and Buttler
as output.
This is the query I am trying :
select substring(employee_Id,1, LOCATE('-',employee_Id) - 1) as Emp_ID from Employee
I am seeing this error:
SQL Error [42815]: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 3 OF SUBSTRING IS INVALID. SQLCODE=-171, SQLSTATE=42815, DRIVER=4.9.78
Edit 1: As suggested by @Mark, I have edited the query as follows
select substring(employee_Id,1, LOCATE('-',employee_Id || '-') - 1) as Emp_ID from Employee
I am getting the same error. I tried to run the LOCATE and found that it is returning the index as 15 for Buttler as the column length is 15.
Run the following as is.
select substring(employee_Id, 1, LOCATE('-', employee_Id || '-') - 1) as Emp_ID
from
(
SELECT 'Jason-21996' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Buttler' FROM SYSIBM.SYSDUMMY1
) Employee (employee_Id);
Does it work for you?