Home » SQL & PL/SQL » SQL & PL/SQL » trim or remove data and spaces (Oracle 10 and 11g)
trim or remove data and spaces [message #601934] Wed, 27 November 2013 09:47 Go to next message
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
Re: trim or remove data and spaces [message #601937 is a reply to message #601934] Wed, 27 November 2013 10:14 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
use substr
Re: trim or remove data and spaces [message #601939 is a reply to message #601937] Wed, 27 November 2013 10:35 Go to previous messageGo to next message
imransi17@gmail.com
Messages: 4
Registered: November 2013
Junior Member
Issue #1 resolved ...Thank you Smile Can you assist in issue #2 plz.
Re: trim or remove data and spaces [message #601940 is a reply to message #601939] Wed, 27 November 2013 10:42 Go to previous messageGo to next message
pablolee
Messages: 2658
Registered: May 2007
Location: Scotland
Senior Member
Use replace
Re: trim or remove data and spaces [message #601944 is a reply to message #601940] Wed, 27 November 2013 11:10 Go to previous messageGo to next message
cookiemonster
Messages: 11285
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.
Re: trim or remove data and spaces [message #601945 is a reply to message #601944] Wed, 27 November 2013 11:19 Go to previous messageGo to next message
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,
Re: trim or remove data and spaces [message #601948 is a reply to message #601945] Wed, 27 November 2013 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/190509/601940/#msg_601940
Re: trim or remove data and spaces [message #601951 is a reply to message #601948] Wed, 27 November 2013 12:47 Go to previous messageGo to next message
pablolee
Messages: 2658
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

Laughing Succinctly put
Re: trim or remove data and spaces [message #601952 is a reply to message #601951] Wed, 27 November 2013 12:49 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
10 GOTO 10

How many folks recognize programming language above?
Re: trim or remove data and spaces [message #601953 is a reply to message #601952] Wed, 27 November 2013 12:52 Go to previous messageGo to next message
pablolee
Messages: 2658
Registered: May 2007
Location: Scotland
Senior Member
Looks a bit like basic (for the spectrum)
Re: trim or remove data and spaces [message #601983 is a reply to message #601953] Thu, 28 November 2013 02:15 Go to previous messageGo to next message
pablolee
Messages: 2658
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
Re: trim or remove data and spaces [message #601988 is a reply to message #601953] Thu, 28 November 2013 02:51 Go to previous messageGo to next message
pablolee
Messages: 2658
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.
Re: trim or remove data and spaces [message #601992 is a reply to message #601953] Thu, 28 November 2013 04:20 Go to previous messageGo to next message
gazzag
Messages: 388
Registered: November 2010
Location: Bristol, UK
Senior Member
BASIC full-stop, mate Smile

Re: trim or remove data and spaces [message #601994 is a reply to message #601992] Thu, 28 November 2013 04:51 Go to previous message
pablolee
Messages: 2658
Registered: May 2007
Location: Scotland
Senior Member
Ha, I'm just chuffed that my old memory managed to recognise it, nevermind the detail around it Wink
Previous Topic: constraints error
Next Topic: Please help me to implement this procedure
Goto Forum:
  


Current Time: Sun Dec 21 23:08:14 CST 2014

Total time taken to generate the page: 0.27418 seconds