Home » SQL & PL/SQL » SQL & PL/SQL » Trimming unseen characters at the end of a string
icon6.gif  Trimming unseen characters at the end of a string [message #238620] Fri, 18 May 2007 13:58 Go to next message
kham2k
Messages: 34
Registered: May 2007
Member
Hi all I have a question

I got a string which got some extra characters added on but they are unseen, i can confirm by checking the length of the string.
I tried to us trim, rtrim but was not really able to solve it.

all the other characters before the unseen ones are digits 0-9

is there any way we can trim them out. also the string is of different length. i mean the string which i need is of different length.

Any suggestions?
Re: Trimming unseen characters at the end of a string [message #238622 is a reply to message #238620] Fri, 18 May 2007 14:06 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
If all the characters you want to keep are [0-9] then use TRANSLATE function.
Re: Trimming unseen characters at the end of a string [message #238623 is a reply to message #238620] Fri, 18 May 2007 14:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select regexp_substr('12345HDHF','[0-9]*') from dual;
REGEX
-----
12345

1 row selected.

Regards
Michel
icon9.gif  Re: Trimming unseen characters at the end of a string [message #238630 is a reply to message #238620] Fri, 18 May 2007 15:42 Go to previous messageGo to next message
kham2k
Messages: 34
Registered: May 2007
Member
i TRIED TO USE REGEXP_SUBST IT IS GIVING ME ERROR INVALID IDENTIFIER.




>>SQL> select regexp_substr('12345HDHF','[0-9]*') from dual;
>>REGEX
>>-----
>>12345

>>1 row selected.
Re: Trimming unseen characters at the end of a string [message #238631 is a reply to message #238622] Fri, 18 May 2007 15:44 Go to previous messageGo to next message
kham2k
Messages: 34
Registered: May 2007
Member
I tried to use that as well but can't get the output rite
if you have suggestions please reply
Re: Trimming unseen characters at the end of a string [message #238637 is a reply to message #238631] Fri, 18 May 2007 15:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I gave a valid answer.
Valid for the version I use.
This is why you have to ALWAYS POST YOUR ORACLE VERSION (4 decimals).
Otherwise You're On Your Own.

Moreover, I posted what I tried. Why didn't you do the same thing?

Regards
Michel
Re: Trimming unseen characters at the end of a string [message #238640 is a reply to message #238620] Fri, 18 May 2007 15:58 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>if you have suggestions please reply
what part of use TRANSLATE function do you not understand?

Is GOOGLE broken for you?

[Updated on: Fri, 18 May 2007 15:58] by Moderator

Report message to a moderator

icon4.gif  Re: Trimming unseen characters at the end of a string [message #238659 is a reply to message #238640] Fri, 18 May 2007 19:07 Go to previous messageGo to next message
kham2k
Messages: 34
Registered: May 2007
Member
with all due respect sir
to use translate function you need to give a defination for unseen character. if you cant see that character how would you define it>

String Length
1112394301251103 24

if you see the visible number are 16 but the length im getting is 24.

so use translate i used following code
TRANSLATE(v.VIN_NBR,'0123456789 ' , '0123456789')
length(TRANSLATE(v.VIN_NBR,'0123456789 ' , '0123456789'))

and also
TRANSLATE(v.VIN_NBR,'0123456789' , '0123456789')
length(TRANSLATE(v.VIN_NBR,'0123456789' , '0123456789'))

but the out come is always the same with the same length.

so here translate can't be used

if there is a way you know that can let translate function to select all the 24 character.
even i tried
TRANSLATE(v.VIN_NBR, v.VIN_NBR , '0123456789')

Do you have anymore suggestions?

I do appreciate you help
Re: Trimming unseen characters at the end of a string [message #238660 is a reply to message #238620] Fri, 18 May 2007 19:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Do you have some aversion against using CUT & PASTE so other can actually see & understand EXACTLY what you are doing & how Oracle is responding?

NONE of what you have posted is a VALID SQL statment.

>This is why you have to ALWAYS POST YOUR ORACLE VERSION (4 decimals).
You seem to be extremely clue resistant.
Re: Trimming unseen characters at the end of a string [message #238675 is a reply to message #238660] Fri, 18 May 2007 23:39 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@kham2k
Quote:
>This is why you have to ALWAYS POST YOUR ORACLE VERSION (4 decimals).
You seem to be extremely clue resistant.

And here's the reason I shut up.
Too bad for you; if you don't answer our question you have no more help.

Regards
Michel
Previous Topic: How to get First 100 odd Numbers using Rownum
Next Topic: Month and day that an employee got hired
Goto Forum:
  


Current Time: Fri Dec 09 08:12:50 CST 2016

Total time taken to generate the page: 0.13274 seconds