Substring in Informix

Question

I need to split a field(Name) into Two (First Name and Last Name) based on a comma in Informix.

Example: "Mark, Wheeler J" has to be split as Mark and Wheeler. I have tried using SQL syntax but it gives me a syntax error in Informix. Please help me.

SELECT
  SUBSTRING(name, 1, CHARINDEX(',', name ) - 1) AS FirstName,
  SUBSTRING(name, CHARINDEX(',', name) + 1, 8000) AS LastName
FROM
 employee

Answer 1

At first if you need to split such values and if you need to do it often then it would be easier to change your db schema:

1. rename column name into last_name
2. add column first_name

It has some advantages. You probably want to search employees by last name, and it is easy when you simply have such column. If last name is a part of name column then you must search using LIKE which is slower and worse.

Now you will have to change some data. If you have comma in last_name then in such column there is first and last name and you must split it.

If you have charindex() function you can do it with:

UPDATE employees SET last_name=substring(last_name FROM charindex(',', last_name)+1), first_name=substring(last_name FROM 1 FOR charindex(',', last_name)-1) WHERE charindex(',', last_name) > 0;

(you can also use TRIM() to remove spaces before/after comma which will be copied)

From comments I see that your version of Informix do not have CHARINDEX() function so you must upgrade db engine or use technique other than clean SQL.

If you can use programming language like Java or Python (for this example I use Jython: it is Python that work in Java environment and can use JDBC driver) you can:

db = DriverManager.getConnection(db_url, usr, passwd)
# prepare UPDATE:
pu = db.prepareStatement("UPDATE employee SET last_name=?, first_name=? WHERE id=?")

# search for names that must be changed:
pstm = prepareStatement("SELECT id, last_name FROM employee WHERE last_name LIKE '%,%')

# for each record found remember its `id`, split `first_name` and update it:

rs = pstm.executeQuery()
while (rs.next()):
    id = rs.getInt(1)
    name = rs.getString(2)
    first_name, last_name = name.split(',')
    pu.setString(1, last_name.strip())
    pu.setString(2, first_name.strip())
    pu.setInt(3, id)
    rc = pu.executeUpdate()