OraFAQ Forum: SQL & PL/SQL » Trimming unseen characters at the end of a string

Home » SQL & PL/SQL » SQL & PL/SQL » Trimming unseen characters at the end of a string

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Trimming unseen characters at the end of a string [message #238620]

Fri, 18 May 2007 13:58

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?

Report message to a moderator

Re: Trimming unseen characters at the end of a string [message #238622 is a reply to message #238620]

Fri, 18 May 2007 14:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If all the characters you want to keep are [0-9] then use TRANSLATE function.

Report message to a moderator

Re: Trimming unseen characters at the end of a string [message #238623 is a reply to message #238620]

Fri, 18 May 2007 14:10

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

1 row selected.

Regards
Michel

Report message to a moderator

Re: Trimming unseen characters at the end of a string [message #238630 is a reply to message #238620]

Fri, 18 May 2007 15:42

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.

Report message to a moderator

Re: Trimming unseen characters at the end of a string [message #238631 is a reply to message #238622]

Fri, 18 May 2007 15:44

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

Report message to a moderator

Re: Trimming unseen characters at the end of a string [message #238637 is a reply to message #238631]

Fri, 18 May 2007 15:53

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Trimming unseen characters at the end of a string [message #238640 is a reply to message #238620]

Fri, 18 May 2007 15:58

BlackSwan
Messages: 26766
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

Re: Trimming unseen characters at the end of a string [message #238659 is a reply to message #238640]

Fri, 18 May 2007 19:07

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

Report message to a moderator

Re: Trimming unseen characters at the end of a string [message #238660 is a reply to message #238620]

Fri, 18 May 2007 19:46

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Trimming unseen characters at the end of a string [message #238675 is a reply to message #238660]

Fri, 18 May 2007 23:39

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 24 09:38:05 CDT 2025