Results 1 to 8 of 8

Thread: SQL statements

  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: SQL statements

    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

  2. #2
    Join Date
    Jan 2007
    Posts
    8
    Get your results and edit the data using a "Find/Replace" in the editor of your choice.

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    Well i need to get the output using a single sql statement.

  4. #4
    Join Date
    Jan 2007
    Posts
    8
    something like:
    Code:
    unload to "/home/suby_george's_directory/email_names.pipe_delimited.txt"
    select tablename.Column_name
    from tablename

  5. #5
    Join Date
    Jan 2007
    Posts
    8
    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
    Last edited by Err; 01-18-07 at 19:18.

  6. #6
    Join Date
    Apr 2004
    Posts
    3
    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

  7. #7
    Join Date
    Jan 2007
    Posts
    8
    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.

  8. #8
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi,

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •