OraFAQ Forum: SQL & PL/SQL » string contains and replace

Home » SQL & PL/SQL » SQL & PL/SQL » string contains and replace

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

string contains and replace [message #9897]

Fri, 12 December 2003 05:54

Alexander
Messages: 109
Registered: May 2000

Senior Member

I need to do an update and convert a string into a number. The field may contain a leading zero, spaces, or letters. If any of these situations occur, I need to remove the value.

example value: fff 0123 needs to be: 123
012345 needs to be: 12345
0123 44 needs to be: 12344
123f044 needs to be: 123044

Report message to a moderator

Re: string contains and replace [message #9901 is a reply to message #9897]

Fri, 12 December 2003 07:48

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Convert any letters to spaces, remove all spaces, convert to number:

sql>select c, 
  2         to_number(
  3           replace(
  4             translate(
  5               lower(c), 
  6               'abcdefghijklmnopqrstuvwxyz',
  7               lpad(' ', 26)),
  8             ' ')) c_number
  9    from t;
 
C                                 C_NUMBER
-------------------------------- ---------
fff 0123                               123
012345                               12345
0123 44                              12344
123f044                             123044
abc
123                                    123

Report message to a moderator

Re: string contains and replace [message #9902 is a reply to message #9901]

Fri, 12 December 2003 08:27

Alexander
Messages: 109
Registered: May 2000

Senior Member

You are fantastic!!!!!!!

One more question. How can I format a where clause to be something like columnvalue "contains" any of the following values:

'abcdefghijklmnopqrstuvwxyz'

Report message to a moderator

Re: string contains and replace [message #9903 is a reply to message #9902]

Fri, 12 December 2003 08:45

Frank Gorman
Messages: 1
Registered: December 2003

Junior Member

This is a great question. My question is how could this be modified to do 1 massive replace of other stray characters, like double quotes, periods, etc.

drop table testtable;
commit;

create table testtable
(testnumber varchar(50) NULL);
commit;

insert into testtable (testnumber) values ('fff 0123');
insert into testtable (testnumber) values ('123 12');
insert into testtable (testnumber) values ('0123 022');
insert into testtable (testnumber) values ('0123 F 022');
insert into testtable (testnumber) values ('0123 F ^&*()" - 022');
insert into testtable (testnumber) values ('0123 F 022');
insert into testtable (testnumber) values ('012f123 ');
commit;

select * from testtable;

select testnumber as OriginalValue,
to_number(
replace(
translate(
lower(testnumber),
'abcdefghijklmnopqrstuvwxyz',
lpad(' ', 26)),
' ')) as NewValue
from testtable;

Report message to a moderator

Re: string contains and replace [message #9904 is a reply to message #9902]

Fri, 12 December 2003 09:13

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

I suppose one way would be:

sql>select * from t;
 
C
--------------------------------
fff 0123
012345
0123 44
123f044
abc
123
 
6 rows selected.
 
sql>select c
  2    from t
  3   where length(c) <> nvl(length(replace(translate(lower(c), 'abcdefghijklmnopqrstuvwxyz', 'a'),
'a')), 0);
 
C
--------------------------------
fff 0123
123f044
abc
 
3 rows selected.

Convert any letters to 'a', strip out all 'a's, and check whether the length has changed.

Report message to a moderator

Re: string contains and replace [message #9905 is a reply to message #9904]

Fri, 12 December 2003 09:34

Alexander
Messages: 109
Registered: May 2000

Senior Member

Thanks for answering....

Is there anyway to wrap the string with something like brackets and search for any occurence of a character contained within it?

e.g. select count(*) from table
where column like '%[[abcdejec&^%$#]]%'

Report message to a moderator

Re: string contains and replace [message #13149 is a reply to message #9902]

Fri, 25 June 2004 09:57

Alexander
Messages: 109
Registered: May 2000

Senior Member

Hi... The problem of trying to identify and fix a column with non-numeric data has again reared its ugly head.

When I issue a select to_number(onsomefield) I get an error.

The code below is what I was trying to use to do a mass update... Can it be modified to take into consideration any other stray characters that are not numeric?

update MyTable
set MyProblemColumn =
to_number(
replace(
translate(
lower(MyProblemColumn),
'abcdefghijklmnopqrstuvwxyz',
lpad(' ', 26)),
' '));

Report message to a moderator

Re: string contains and replace [message #13154 is a reply to message #13149]

Sat, 26 June 2004 13:10

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

You can just expand Todd's method to include the other characters:

update MyTable
set MyProblemColumn =
to_number( replace( translate( lower(MyProblemColumn),
'abcdefghijklmnopqrstuvwxyz!@#$%^&*()_-+={[[}]]&#124:;"''<,>.?/',
lpad(' ', 59)), ' '));

Report message to a moderator

Previous Topic:	PURITY LEVEL OF FUNCTIONS
Next Topic:	merge statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 04:24:20 CDT 2024