Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jun 2003
    Posts
    38

    Post Unanswered: Rtrim specific charecter in DB2

    Hi,

    I have following SQL statement in Oracle PL/SQl

    strinsert := RTRIM(strinsert,',')

    Here RTRIM() function takes two argumnet. It removes the right most apperaence of the character mentioned in the second paprameter.

    How can I get the same in Db2.

    Thanks,
    Raj Shekhar

  2. #2
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    You can accomplish this by using the lenght and substr functions:

    Substr(strinsert,1,(length(strinsert)-1)) for varchar datatype

    and

    Substr(strinsert,1,(length(rtrim(strinsert))-1)) for char datatype
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  3. #3
    Join Date
    Jun 2003
    Posts
    38

    Thumbs up

    Originally posted by blom0344
    You can accomplish this by using the lenght and substr functions:

    Substr(strinsert,1,(length(strinsert)-1)) for varchar datatype

    and

    Substr(strinsert,1,(length(rtrim(strinsert))-1)) for char datatype
    Thanks Blom,
    It worked. But could not understand the use of RTRIM for char data type as u mentioned in your solution.

    Secondly, can you please look into another query reagarding dblink ie. connecting to another database dynamically. In Oracle , we use the concept of DBLINk for connecting from one database to another or to different users. How about DB2 ?
    Raj Shekhar

  4. #4
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    With char datatype you have to trim off the trailing spaces , otherwise you will be trimming off a trailing space instead of the last character of the string. (You know , if you have 'HOLIDAY' in a CHAR(10) there are 3 trailing spaces....

    Database links: you should look for 'federated' databases / DB2 relational connect. I think that ORACLE tackled this area earlier than DB2......
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Why not create a UDF with the available info ?

    Cheers

    Sathyaram

  6. #6
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Originally posted by sathyaram_s
    Why not create a UDF with the available info ?

    Cheers

    Sathyaram

    Can you elaborate a bit further on this topic............?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Not sure the syntax or the output, but, as an example,

    CREATE FUNCTION abcd(X CHAR(100))
    RETURNS CHAR(100)
    LANGUAGE SQL
    CONTAINS SQL
    NO EXTERNAL ACTION
    DETERMINISTIC
    RETURN Substr(X,1,(length(X)-1))

    and use select abcd(strinsert) when needed rather than Substr(X,1,(length(X)-1)) each time ...

    Correct me if I'm wrong ...

    Cheers

    Sathyaram

  8. #8
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Function like that would be a solution, you still need to check first if you are performing it on varchar or char. It will always work with varchar, whereas with char you will be surprised that your data appears to be the same as the input............................
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you define the function argument as VARCHAR and define the function to return Substr(strinsert,1,(length(rtrim(strinsert))-1)) , I think you will get the proper result set ... Is it not ?

    Cheers

    Sathyaram

  10. #10
    Join Date
    Jun 2003
    Posts
    38

    Lightbulb

    Originally posted by sathyaram_s
    Not sure the syntax or the output, but, as an example,

    CREATE FUNCTION abcd(X CHAR(100))
    RETURNS CHAR(100)
    LANGUAGE SQL
    CONTAINS SQL
    NO EXTERNAL ACTION
    DETERMINISTIC
    RETURN Substr(X,1,(length(X)-1))

    and use select abcd(strinsert) when needed rather than Substr(X,1,(length(X)-1)) each time ...

    Correct me if I'm wrong ...

    Cheers

    Sathyaram

    Hi Sathya/Blom,

    As u suggested, I have written the UDF for RTRIM with an additional parameter, the search charecter. If the search charecter matches the last charecter of the source string then return source string minus last charecter . ie if source string= 'raj,' and search charecter is ',' the it should return 'raj' only. The following source code compiled and run but result is not expected. Can u please have a look and suggest what is missing ?

    CREATE FUNCTION UDF_RTRIM (SRC_STR VarChar(4000), SEARCH_CHAR Char(1))
    RETURNS VARCHAR(4000)
    LANGUAGE SQL
    SPECIFIC RPADBase
    DETERMINISTIC
    CONTAINS SQL
    NO EXTERNAL ACTION
    RETURN
    CASE
    WHEN (SUBSTR(SRC_STR,LENGTH(SRC_STR) -1,LENGTH(SRC_STR)) = SEARCH_CHAR) THEN
    SUBSTR(SRC_STR,1,LENGTH(SRC_STR) - 1)
    END
    Raj Shekhar

  11. #11
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Yes, indeed you should have it tackled then.

    It seems that control center does not offer a way to create new user functions. (just a list to filter on) Issuing the DDL through command center to create function?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  12. #12
    Join Date
    Jun 2003
    Posts
    38
    Originally posted by blom0344
    Yes, indeed you should have it tackled then.

    It seems that control center does not offer a way to create new user functions. (just a list to filter on) Issuing the DDL through command center to create function?

    I am using Development center. I think there is some problem in condition. When I run only with following statement in the udf

    SUBSTR(SRC_STR,1,LENGTH(SRC_STR) - 1)

    then it return correct value. Can u please see the condition. The purpose of putting condiotion is as mentioned earlier.
    Thanks
    Raj Shekhar

  13. #13
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Should the case statement no include an 'else' option?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  14. #14
    Join Date
    Jun 2003
    Posts
    38
    Originally posted by blom0344
    Should the case statement no include an 'else' option?
    I don't think so (but not sure).
    Raj Shekhar

  15. #15
    Join Date
    Jun 2003
    Posts
    38

    Thumbs up

    Originally posted by Raj Shekhar
    I don't think so (but not sure).
    Well,
    It worked. Condition should be this :

    WHEN (SUBSTR(SRC_STR,LENGTH(SRC_STR) ,LENGTH(SRC_STR)-1) = SEARCH_CHAR) THEN

    SUBSTR(SRC_STR,1,LENGTH(SRC_STR) - 1)

    and not as mentioned earlier.
    We can customize more as per the need.
    Thanks
    Raj Shekhar

Posting Permissions

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