-
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
-
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
-
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
-
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
-
i also have 'AVE' ,'BLVD' to be removed at the end
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|