| Replace in Oracle Forms [message #599026] |
Mon, 21 October 2013 08:19  |
 |
Hometown1
Messages: 23
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  |
 |
mughals_king
Messages: 392
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 |
 |
Littlefoot
Messages: 21808
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> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
