SQL statements

suby_george · #1 (**permalink**) 01-17-07, 09:15

Hi All,

Can you please help me with this?

how to use the built in string function of informix.
Lets say i have one coulumn of a table which stores the e-mail ids.
for e.g. abc@xyz.com
abacd@xyz.com

I want to fetch only the user names from the email id's leaving the suffix from the column like from "abc@xyz.com" i need only "abc". This is required in a select statement.

Thanks is advance.
SG

Err · #2 (**permalink**) 01-17-07, 16:33

Get your results and edit the data using a "Find/Replace" in the editor of your choice.

suby_george · #3 (**permalink**) 01-18-07, 07:43

Well i need to get the output using a single sql statement.

Err · #4 (**permalink**) 01-18-07, 10:28

something like:

Code:

unload to "/home/suby_george's_directory/email_names.pipe_delimited.txt"
select tablename.Column_name
from tablename

Err · #5 (**permalink**) 01-18-07, 18:00

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.

Something like:

Code:

select TRIM(TRAILING ' ' FROM SUBSTR(REPLACE(tablename.Column_name, '@',
'                                                                '), 0, 65))
 from tablename

suby_george · #6 (**permalink**) 01-19-07, 04:29

Thanks a lot,

I tried using the REPLACE function, but getting the following error:
"SQL error -674 : Procedure (replace) not found. ISAM error -111: ISAM error: no record found"

We are using the following version of Informix "Informix Dynamic Server Version 7.31.UD3". Does this function are with specific version.

Any pointers!!!

Thanks in advance

Err · #7 (**permalink**) 01-19-07, 10:49

Here's a little story:

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.

Tyveleyn · #8 (**permalink**) 01-22-07, 04:23

Hi,

Quote:

I want to fetch only the user names from the email id's leaving the suffix from the column like from "abc@xyz.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.

Code:

CREATE PROCEDURE "informix".pos(search VARCHAR(255), source VARCHAR(255))        
    RETURNING SMALLINT;                                                     
                                                                            
    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                                 
                        RETURN 0;                                           
                    END IF                                                  
                    WHILE j <= l1                                           
                        IF substr(search,j,1) != substr(source,i+j-1,1) THEN
                            EXIT WHILE;                                     
                        END IF                                              
                        LET j = j + 1;                                      
                    END WHILE                                               
                END IF                                                      
                IF j > l1 THEN                                              
                    RETURN i;                                               
                END IF                                                      
            END IF                                                          
        END FOR                                                             
    END IF                                                                  
    RETURN 0;                                                               
                                                                            
END PROCEDURE

It's not fast, but once deployed it can be used in the following way:

Code:

SELECT substr(email, 1, pos("@", email)-1)
FROM contact

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.

Regards

Internet Services

Web Development

Digital Marketing

Consumer Tech