Here are two possible solutions for what seems like a homework assignment. What is not known is if you have access to the enhanced ESQL libraries or not. That would make things easier.
The most obvious solution is to simply use the replace function to replace '@' with a '|' -then you have the field seperated nicely. A more ugly solution is to replace '@' with a whole lot of spaces then take what would seem like the limit for the first part of an address and trim off the extra space. Let's do that.
select TRIM(TRAILING ' ' FROM SUBSTR(REPLACE(tablename.Column_name, '@',
' '), 0, 65))
I have a problem, I'm trying to cross the street. So I call my friend and ask him how to cross the street.
"Look both ways, if there is no traffic coming - walk across the street"
"But the traffic won't stop coming" I say.
"Then go to the nearest crosswalk, and push the walk button. When the light changes to red -the traffic will stop and you may cross."
"But I'm on the freeway-"....
Do you see my point? I didn't give enough information to solve the problem.
My original answer to the sql string problem was to use the right tool for the job. SQL is a "Query Language" which means it is used for retrieving data from a database. If you must edit the data once it is out of the database -use a more apropriate tool.
Otherwise... try explaining what it is you are trying to do. Give as much detail and context as you can. Informix 7.x is beyond my scope.
I want to fetch only the user names from the email id's leaving the suffix from the column like from "email@example.com" i need only "abc". This is required in a select statement.
Once I needed a similar functionality and for that I created a stored function (stored procedure that returns a value in 7.30 UDS). It's the SPL equivalent to builtin functions in many programming languages; this one's named after the equivalent Delphi function.
CREATE PROCEDURE "informix".pos(search VARCHAR(255), source VARCHAR(255))
DEFINE i SMALLINT;
DEFINE j SMALLINT;
DEFINE l1 SMALLINT;
DEFINE l2 SMALLINT;
LET l1 = LENGTH(search);
LET l2 = LENGTH(source);
IF l1 > 0 AND l2 >= l1 THEN
FOR i IN (1 TO l2)
IF substr(search,1,1) = substr(source,i,1) THEN
LET j = 2;
IF l1 > 1 THEN
IF l1 - 1 > l2 - i THEN
WHILE j <= l1
IF substr(search,j,1) != substr(source,i+j-1,1) THEN
LET j = j + 1;
IF j > l1 THEN
It's not fast, but once deployed it can be used in the following way:
SELECT substr(email, 1, pos("@", email)-1)
where the SUBSTR function returns the string that starts at position 1 of the email address and runs through the (first) position of the @ sign minus 1.