OraFAQ Forum: SQL & PL/SQL » trim or remove data and spaces

Home » SQL & PL/SQL » SQL & PL/SQL » trim or remove data and spaces (Oracle 10 and 11g)

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

trim or remove data and spaces [message #601934]

Wed, 27 November 2013 09:47

imransi17@gmail.com
Messages: 4
Registered: November 2013

Junior Member

Hello,

I am trying to remove/trim everything after 7 characters.

**issue #1** removing everything after 6 or 7 chracters
example: 1Q7 4B7 MY NAME IS MARY

Results I want: 1Q7 4B7

**issue #2** removing one space
example: EQ9 2IQ
Results I want: EQ9 2IQ

Please assist.

Thanks

Report message to a moderator

Re: trim or remove data and spaces [message #601937 is a reply to message #601934]

Wed, 27 November 2013 10:14

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

use substr

Report message to a moderator

Re: trim or remove data and spaces [message #601939 is a reply to message #601937]

Wed, 27 November 2013 10:35

imransi17@gmail.com
Messages: 4
Registered: November 2013

Junior Member

Issue #1 resolved ...Thank you Smile

Can you assist in issue #2 plz.

Report message to a moderator

Re: trim or remove data and spaces [message #601940 is a reply to message #601939]

Wed, 27 November 2013 10:42

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Use replace

Report message to a moderator

Re: trim or remove data and spaces [message #601944 is a reply to message #601940]

Wed, 27 November 2013 11:10

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

I can't tell from your example where this space you want removing is, but if it's on the end I'm still going with substr.

Report message to a moderator

Re: trim or remove data and spaces [message #601945 is a reply to message #601944]

Wed, 27 November 2013 11:19

imransi17@gmail.com
Messages: 4
Registered: November 2013

Junior Member

**issue #2** removing one space
example: EQ9 2IQ
Results I want: EQ9 2IQ

There suppose to be 1 space between EQ9 and 2IQ but some records have 2 spaces between EQ9 and 2IQ. My goal is to have 1 space.

Thanks,

Report message to a moderator

Re: trim or remove data and spaces [message #601948 is a reply to message #601945]

Wed, 27 November 2013 11:51

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/190509/601940/#msg_601940

Report message to a moderator

Re: trim or remove data and spaces [message #601951 is a reply to message #601948]

Wed, 27 November 2013 12:47

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Michel Cadot wrote on Wed, 27 November 2013 17:51

http://www.orafaq.com/forum/mv/msg/190509/601940/#msg_601940

Succinctly put

Report message to a moderator

Re: trim or remove data and spaces [message #601952 is a reply to message #601951]

Wed, 27 November 2013 12:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

10 GOTO 10

How many folks recognize programming language above?

Report message to a moderator

Re: trim or remove data and spaces [message #601953 is a reply to message #601952]

Wed, 27 November 2013 12:52

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Looks a bit like basic (for the spectrum)

Report message to a moderator

Re: trim or remove data and spaces [message #601983 is a reply to message #601953]

Thu, 28 November 2013 02:15

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

This s a massively over complex solution to a very simple problem. Replace is the correct way to go, all you are doing is adding confusion

Report message to a moderator

Re: trim or remove data and spaces [message #601988 is a reply to message #601953]

Thu, 28 November 2013 02:51

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

I didn't even bother assessing whether the code would achieve the required results as it is pointless to use such a convoluted method when a simple one is readily available.

Report message to a moderator