-
trim on column
I have column where the application insert date and productcomments
Date - Purchasereason
01/19/2005 - product purchased:100000. forupgrade
02/30/2005 - Product Purchased:134783. for newins office213
Now i need to display the number part only(100000 and 134783)
if i use ltrim i am able to remove intial part
How do i get only like 134783 or 100000
any little help is much appreciated
-
If it's always EXACTLY in the same place:
SUBSTR(my_string, 30, 6) will find it.
(the 30 is approximate, I'm incapable of counting correctly - for me it goes: 1,2,3,more,lots,too-many.)
Otherwise you'll have to use INSTR to find the : and . and build a substr with the result.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Yes , it starts every time at the same place but the number varies , some times it will be 5 and some times it could be 7
Thanks for the help
Originally posted by DaPi
If it's always EXACTLY in the same place:
SUBSTR(my_string, 30, 6) will find it.
(the 30 is approximate, I'm incapable of counting correctly - for me it goes: 1,2,3,more,lots,too-many.)
Otherwise you'll have to use INSTR to find the : and . and build a substr with the result.
-
Then it will be roughly:
SUBSTR(my_string,
INSTR(my_string, ':') + 1,
INSTR(my_string, '.') - INSTR(my_string, ':') - 1)
Now the question is, can you rely on the : and . always being there?
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Originally posted by DaPi
Then it will be roughly:
SUBSTR(my_string,
INSTR(my_string, ':') + 1,
INSTR(my_string, '.') - INSTR(my_string, ':') - 1)
Now the question is, can you rely on the : and . always being there?
You beat me to it!
Code:
SELECT SUBSTR('01/19/2005 - product purchased:100000. forupgrade',
INSTR('01/19/2005 - product purchased:100000. forupgrade', ':', 20) + 1,
INSTR('01/19/2005 - product purchased:100000. forupgrade', '.') -
INSTR('01/19/2005 - product purchased:100000. forupgrade', ':', 20) - 1 )
FROM dual;
-
thank you guys ! you are awesome!!
i was trying and hung something like SUBSTR(Purchase,30,INSTR(purchase, ':')
You guys made it easy ..thanks it works , yes the application inserts ':' and '.'
Thanks for your time.
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
|