CODEWITHSUNDEEP
sqldb2webspherezos
Varsha Kumari
Varsha Kumari

Reputation: 33

How to get a character delimited substring in DB2?

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.

Upvotes: 0

Views: 2782

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12314

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?

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269663

If your version of DB2 supports regular expressions, then a simple method is regexp_substr():

regexp_substr(employee_id, '^[^-]+')

Here is a db<>fiddle.

Upvotes: 0

Related Questions