Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    107

    Unanswered: Oracle Ltrim with 2 arguments conversion in SQL Server

    How do I convert Oracle's LTRIM(char, set) to SQL Server?

    Thanks,
    Jake

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You mean like..


    DECLARE @x char(100)
    SELECT @x = ' Brett '
    SELECT '"'+@x+'"'
    SELECT '"'+RTRIM(LTRIM(@x))+'"'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    107
    Originally posted by Brett Kaiser
    You mean like..


    DECLARE @x char(100)
    SELECT @x = ' Brett '
    SELECT '"'+@x+'"'
    SELECT '"'+RTRIM(LTRIM(@x))+'"'

    I give you an example oracle Query:

    Select RTRIM('abcdeee', 'e') from dual


    Thanks,
    Jake

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Man...it's been awhile for Oracle...damn I got get back to it...

    Select REPLACE('abcdeee', 'e')

    But it will be all chars in the string.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What version are you talking about?

    http://www.cit.uws.edu.au/docs/oracl...on.htm#1025279
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Posts
    107
    hi Brett,

    I think i'm not clear in my previous mail. Oracle 8 has an in-built function RTRIM with 2 arguments, namely 'char' and 'set'. It returns char, with all the rightmost characters that appear in 'set' removed.
    Example:
    SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g."
    FROM DUAL;

    RTRIM e.g
    -------------
    BROWNINGyxX


    http://www.cit.uws.edu.au/docs/oracl...on.htm#1025070
    Is there any equivalent in-built function in SQL server?

    Hope I'm clear now. Appreciate Your Help.
    Jake

    Originally posted by Brett Kaiser
    What version are you talking about?

    http://www.cit.uws.edu.au/docs/oracl...on.htm#1025279

  7. #7
    Join Date
    Feb 2004
    Posts
    107
    anybody know how to do this.

    Oracle 8 has an in-built function RTRIM with 2 arguments, namely 'char' and 'set'. It returns char, with all the rightmost characters that appear in 'set' removed.
    Example:
    SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g."
    FROM DUAL;

    RTRIM e.g
    -------------
    BROWNINGyxX

    Is there any equivalent in-built function in SQL server?
    If not, how to achieve this???

    Jake

    Originally posted by Jake K
    hi Brett,

    I think i'm not clear in my previous mail. Oracle 8 has an in-built function RTRIM with 2 arguments, namely 'char' and 'set'. It returns char, with all the rightmost characters that appear in 'set' removed.
    Example:
    SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g."
    FROM DUAL;

    RTRIM e.g
    -------------
    BROWNINGyxX


    http://www.cit.uws.edu.au/docs/oracl...on.htm#1025070
    Is there any equivalent in-built function in SQL server?

    Hope I'm clear now. Appreciate Your Help.
    Jake

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Oracle Ltrim with 2 arguments conversion in SQL Server

    Would this work for you:
    Code:
    select left('BROWNINGyxXxy', PATINDEX('%xy%','BROWNINGyxXxy') - 1)
    I don't think it's precisely identical, but it yields the same result for your example:

    BROWNINGyxX


    Regards,

    hmscott


    Originally posted by Jake K
    How do I convert Oracle's LTRIM(char, set) to SQL Server?

    Thanks,
    Jake

  9. #9
    Join Date
    Feb 2004
    Posts
    107

    Re: Oracle Ltrim with 2 arguments conversion in SQL Server

    thanks. It's working fine for me after doing a small correction - changed %xy% to %xy. If %xy% is used, the first occurence of xy will be return even if it is present in the mid of the char. i.e. for this string 'BROWNINGyxyXxy', the result will be 'BROWNINGy' but what the Oracle's RTRIM returns is 'BROWNINGyxyX'. If the pattern is changed to '%xy', then we will get the same result as the oracle's rtrim. hope i'm clear.

    Once again thanks for the help.

    Jake

    Originally posted by hmscott
    Would this work for you:
    Code:
    select left('BROWNINGyxXxy', PATINDEX('%xy%','BROWNINGyxXxy') - 1)
    I don't think it's precisely identical, but it yields the same result for your example:

    BROWNINGyxX


    Regards,

    hmscott

Posting Permissions

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