DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: string check

  1. #1
    Join Date
    Jun 2002
    Posts
    17
    I want to check the last two characters in
    200 WILMOT RD if it is 'RD' i want 200 WILMOT

    how do i do it

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Substr() function should be your friend.

    It should be lsomewhat ike :
    SELECT SUBSTR('200 WILMOT RD',1,LENGTH('200 WILMOT RD') - 2) "Substring"
    FROM DUAL;


    Hope this helps.




    -- Dilip

  3. #3
    Join Date
    Apr 2002
    Posts
    9
    Use this expression

    decode(upper(substr(st,-2,2)),'RD',substr(st,1,length(st)-2))

    where st is the input string.

    you may want to create a function using this expression.

    david

  4. #4
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    IF the output requires all characters in string except last two, then substr should suffice.

    Else if it is some predetermined value like 'RD' then SUBSTR and DECODE in combination like david mentioned should help.
    -- Dilip

  5. #5
    Join Date
    Jun 2002
    Posts
    17
    i also have 'AVE' ,'BLVD' to be removed at the end

  6. #6
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    What criteria are you using to cut the string? Is it the fixed number of characters from begining or from end? In either case, the above should help.
    -- Dilip

  7. #7
    Join Date
    Dec 2000
    Posts
    126
    try this - assuming there is a space before rd /ave/st


    SQL> select substr(upper('123 first ave'),1,instr(upper('123 first ave'),' ',-1))
    2 from dual
    3 ;

    SUBSTR(UPP
    ----------
    123 FIRST

    SQL>

Posting Permissions

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


Click Here to Expand Forum to Full Width