Home » SQL & PL/SQL » SQL & PL/SQL » Remove characters from field (9i)
Remove characters from field [message #323209] Tue, 27 May 2008 14:47 Go to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
I have tried searching and apparently am using the wrong terms for search, therefore my search has come up empty.

I have a phone number field. What I would like to do is remove any character that is not 0-9 from the field and the take the first ten digits. I could use a replace or translate but that seems messy. Is there a better way to do this?
Re: Remove characters from field [message #323212 is a reply to message #323209] Tue, 27 May 2008 15:06 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
But TRANSLATE is the way to go.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions196.htm#i1501659
Re: Remove characters from field [message #323214 is a reply to message #323212] Tue, 27 May 2008 15:18 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
my translate would be well over 50 characters when you take into consideration the upper and lower case as well as any symbols, just thought there may be an easier way to do it.
Re: Remove characters from field [message #323216 is a reply to message #323209] Tue, 27 May 2008 15:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>my translate would be well over 50 characters
Are you being charged by the character?

Re: Remove characters from field [message #323219 is a reply to message #323214] Tue, 27 May 2008 16:17 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
And what have you tried so far?

It's possible with AT MOST an 11 character translate and some other functions.
Re: Remove characters from field [message #323220 is a reply to message #323219] Tue, 27 May 2008 16:20 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
I ended up using a translate with an 83 character long field and it works fine, just thought there may be a cleaner way to do it to ensure I did not miss anything.
Re: Remove characters from field [message #323221 is a reply to message #323220] Tue, 27 May 2008 16:21 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
There is a cleaner way. How can you be certain those 83 characters cover everything?
Re: Remove characters from field [message #323223 is a reply to message #323221] Tue, 27 May 2008 16:43 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
Exactly my point, would you please share this other way Smile
Re: Remove characters from field [message #323227 is a reply to message #323223] Tue, 27 May 2008 18:08 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
While I agree with Anacedent, you ask for another way so here is one using pl/sql:

psuedocode:
#1 Trim spaces.

TRIM(somefield) -easy enough

#2 Internal removal for spaces or any other character:

While INSTR(somefield,somechar) LOOP
somefield = replace(somefield,somechar)
END LOOP

--now if you dont know upfront what the gobbleygook chars are
--then:
pos  as integer start val of 1
L  length of somefield (field to work on)
somefield2  output field we will put result in

for pos in 1 .. L LOOP
  if SUBSTR(somefield,POS,1) in ('0','1','2','3',etc to 9)
  then
    somefield2 := somefield2 || substr(somefield,pos,1);
  end-if;
END LOOP; 


crude approach

Best Regards,
Harry



Re: Remove characters from field [message #323229 is a reply to message #323223] Tue, 27 May 2008 18:16 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Just some sample data with different characters:

SQL> select * from phone_number;

NUM
----------------------------------------
abc-def-ghij1234567890
456-456-7890
888.456.7890
a99-b34-c567-890
#90-909-38290
        231-456-7890     <----- tab character at beginning
!1@2#$%^78&*()-+0-456-9012345

7 rows selected.

SQL> select substr(replace(translate(num, translate(num, ' 1234567890', ' '), ' '), ' '), 1, 10) "1st 10 digits"
  2  from phone_number;

1st 10 digits
-------------
1234567890
4564567890
8884567890
9934567890
9090938290
2314567890
1278045690

7 rows selected.

[Updated on: Tue, 27 May 2008 18:21]

Report message to a moderator

Re: Remove characters from field [message #323303 is a reply to message #323229] Wed, 28 May 2008 02:32 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you were on 10g (or above), you might use regular expressions, such as
SQL> select
  2    num,
  3    substr(regexp_replace(num, '[^[:digit:]]'), 1, 10) "1st 10 digits"
  4  from test;

NUM                            1st 10 digits
------------------------------ --------------------
abc-def-ghij1234567890         1234567890
456-456-7890                   4564567890
888.456.7890                   8884567890
a99-b34-c567-890               9934567890
#90-909-38290                  9090938290
        231-456-7890           2314567890
!1@2#$%^78&*()-+0-456-9012345  1278045690

7 rows selected.

SQL>
Or, if you simply LOVE to use regexp, install 10g XE, do some data transferring between your 9i and newly installed 10g, do the processing, move data back. However, I believe it's not worth it when Brian's solution does the job.
Re: Remove characters from field [message #323359 is a reply to message #323229] Wed, 28 May 2008 05:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Very nice!!!
Re: Remove characters from field [message #323408 is a reply to message #323359] Wed, 28 May 2008 07:31 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you! ./fa/2877/0/
Previous Topic: how can i grant privilege to new user
Next Topic: Grant - Table & Trigger.
Goto Forum:
  


Current Time: Sat Dec 10 10:39:47 CST 2016

Total time taken to generate the page: 0.16611 seconds