Home » Developer & Programmer » Forms » Replace in Oracle Forms (Oracle Forms 6i with oracle database 8i)
Replace in Oracle Forms [message #599026] Mon, 21 October 2013 08:19 Go to next message
Hometown1
Messages: 21
Registered: March 2011
Junior Member
I have a column named account_detail of data type Varchar2(70). I want to enforce a law such that all data being entered in this column is entered as

22 22 22 22

which means there is a space after every two digits. How do I achieve this through oracle forms, is there a way in the property pallette through which I can set it's property to reflect this change or should I do this with key-next-item, if yes then how?

Please guide

[Updated on: Mon, 21 October 2013 13:49]

Report message to a moderator

Re: Replace in Oracle Forms [message #599045 is a reply to message #599026] Mon, 21 October 2013 15:10 Go to previous messageGo to next message
mughals_king
Messages: 351
Registered: January 2012
Location: pakistan
Senior Member
try this
select regexp_replace('22     22         22      22','[[:blank:]]{2,8}',' ') from dual;

select regexp_replace('MUGHALSKING', '(.)', '\1 ') as Output from dual;

SELECT
  REGEXP_REPLACE(account_detail , '(.)', '\1 ') "REGEXP_REPLACE"
  FROM TABLE_NAME;



Hope you got it

Regard
mughal

[Updated on: Mon, 21 October 2013 15:12]

Report message to a moderator

Re: Replace in Oracle Forms [message #599072 is a reply to message #599045] Tue, 22 October 2013 01:37 Go to previous message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
mughals_king: I don't know whether he got it, but none of your examples answers the question. Did you try them? The first one leaves 2 spaces in the middle of the string; the second one puts a space after every character (including spaces), so ... what was your point?

SQL> select regexp_replace('22     22         22      22','[[:blank:]]{2,8}',' ') from dual;

REGEXP_REPLA
------------
22 22  22 22

SQL>
SQL> select regexp_replace('M  UGHALSKING', '(.)', '\1 ') as Output from dual;

OUTPUT
--------------------------
M     U G H A L S K I N G

SQL>




Hometown1: as you want to "enforce" such a constraint, you have two options: one is to create a column constraint (which is a good idea, because it would prevent invalid values entered by any tool, including Forms, SQL*Plus, etc.). Its drawback is that error user gets isn't very descriptive ("check constraint CH_2DIG_SPACE violated", for example, if CH_2DIG_SPACE was constraint name). Therefore, you might - additionally - create a WHEN-VALIDATE-ITEM that would check whether value entered into an item complies the constraint.

As you use Forms 6i (I don't), I'm not sure that regular expressions are supported in that version. If not, create a database function and call it from the WHEN-VALIDATE-ITEM trigger. If the pattern is wrong, raise an alert and inform end user (and, possibly, explain format you expect to be entered).

Here's one option which is rather straightforward: valid values contain 2 digits (\d{2}) followed by a space (\s); repeat that 3 times, while the last part contains only 2 digits:
SQL> with test as
  2    (select '22 33 44 55' col from dual union
  3     select 'xy zz 44 55'     from dual union
  4     select '123 4 55 78'     from dual union
  5     select '12345678'        from dual union
  6     select '11  22 33 44'    from dual union
  7     select '01 22 34 56 78'  from dual
  8    )
  9  select col
 10  from test
 11  where regexp_like(col, '^\d{2}\s\d{2}\s\d{2}\s\d{2}$');

COL
--------------
22 33 44 55

SQL>
Previous Topic: random selection
Next Topic: enter arabic in english text field
Goto Forum:
  


Current Time: Sat Apr 19 13:35:25 CDT 2014

Total time taken to generate the page: 0.09543 seconds