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()

Answer 2

I have faced a similar problem so I have developed a below function "char_cut" for informix 11.50 (haven't tried on different versions) Be warn that this is not the most efficient way to do that, but it works fine.

Usage:

SELECT
 char_cut(name, ',', 1) AS FirstName,
 char_cut(name, ',', 2) AS LastName
FROM
employee

Procedure:

create procedure char_cut( str_in varchar(255), separator_in char(1), field_in int  )
returning varchar(255) ;

define res varchar(255);
define str_len int;
define pos_curr int;
define substr_start int;
define substr_length int;
define pos_char char(1);

IF field_in <= 0 THEN return ''; END IF;

LET res = '';
LET substr_start = 0;
LET substr_length = 0;
LET str_len = length(str_in);

FOR pos_curr = 1 TO str_len

   LET pos_char = substr(str_in,pos_curr,1);
   IF pos_char = separator_in THEN
      LET field_in = field_in - 1;
   END IF;

   IF field_in = 1 and substr_start = 0 THEN
      LET substr_start = pos_curr + DECODE(pos_char,separator_in,1,0);
   END IF;

   IF field_in <= 0 THEN
      LET substr_length = pos_curr;
      EXIT FOR; --KONIEC 
   END IF;

END FOR;

IF substr_length = 0 THEN
   LET substr_length = str_len+1;
END IF;

IF substr_start = 0 THEN
   LET substr_start = str_len+1;
END IF;

IF substr_length < substr_start THEN
   LET substr_length = 0;
ELSE
   LET substr_length = substr_length - substr_start;
END IF;

RETURN NVL(substring ( str_in from substr_start for substr_length ),'');

end procedure;