Home » Developer & Programmer » Forms » how to remove special characters from oracle forms fields? (Oracle Forms 6i)
how to remove special characters from oracle forms fields? [message #606671] Tue, 28 January 2014 03:30 Go to next message
jineesh
Messages: 14
Registered: January 2014
Junior Member
how to remove special characters from oracle forms fields?
Re: how to remove special characters from oracle forms fields? [message #606674 is a reply to message #606671] Tue, 28 January 2014 03:37 Go to previous messageGo to next message
jineesh
Messages: 14
Registered: January 2014
Junior Member
I mean Oracle form 6i .if anybody know ,help me.
Re: how to remove special characters from oracle forms fields? [message #606675 is a reply to message #606671] Tue, 28 January 2014 03:37 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Use the backspace key
Re: how to remove special characters from oracle forms fields? [message #606676 is a reply to message #606674] Tue, 28 January 2014 03:38 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Ahhh, 6i, in that case, use the delete key.
Re: how to remove special characters from oracle forms fields? [message #606677 is a reply to message #606675] Tue, 28 January 2014 03:39 Go to previous messageGo to next message
jineesh
Messages: 14
Registered: January 2014
Junior Member
i mean data will be like this "543543543-6465456,5666746-5354354".here i need to remove "'-',',' ".
Re: how to remove special characters from oracle forms fields? [message #606678 is a reply to message #606677] Tue, 28 January 2014 03:44 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
REPLACE can do that.
Re: how to remove special characters from oracle forms fields? [message #606680 is a reply to message #606676] Tue, 28 January 2014 03:45 Go to previous messageGo to next message
jineesh
Messages: 14
Registered: January 2014
Junior Member
this is what am not playing game or what.
Re: how to remove special characters from oracle forms fields? [message #606684 is a reply to message #606680] Tue, 28 January 2014 03:49 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
LF isn't playing a game, he told you what function to use. If you don't know what it does look it up in the docs
Re: how to remove special characters from oracle forms fields? [message #606690 is a reply to message #606680] Tue, 28 January 2014 03:59 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
jineesh wrote on Tue, 28 January 2014 09:45
this is what am not playing game or what.

You supply vague details with your question, we supply vague (but correct) solutions. You want more detail? Provide more (accurate) detail.
Re: how to remove special characters from oracle forms fields? [message #606693 is a reply to message #606690] Tue, 28 January 2014 04:03 Go to previous messageGo to next message
jineesh
Messages: 14
Registered: January 2014
Junior Member
now I have value like this "9787-765675765,5645-654545656,5665465-65456456,67446466656'

and I have to get like this

9787765675765
5645654545656
566546565456456
67446466656

On oracle Forms 6i by Pl/Sql
Re: how to remove special characters from oracle forms fields? [message #606695 is a reply to message #606693] Tue, 28 January 2014 04:06 Go to previous messageGo to next message
jineesh
Messages: 14
Registered: January 2014
Junior Member
I tried like this

Select Col1
from (select regexp_substr(:p_Tel,'[^,]+', 1,Level) col1 from dual
connect by regexp_substr(:p_Tel,'[^,]+', 1,Level) Is not Null);

But, regexp_substr not supporting on Oracle forms 6i.


Whit this query am getting value on sql.

[Updated on: Tue, 28 January 2014 04:08]

Report message to a moderator

Re: how to remove special characters from oracle forms fields? [message #606699 is a reply to message #606695] Tue, 28 January 2014 04:13 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
You know there's a big difference between:
remove '-'
and
remove '-' and break up the result into seperate lines based on ','

Do these seperate lines need to go into seperate records in the form?
Re: how to remove special characters from oracle forms fields? [message #606701 is a reply to message #606699] Tue, 28 January 2014 04:26 Go to previous messageGo to next message
jineesh
Messages: 14
Registered: January 2014
Junior Member
if we will get data without '-' this ,I can separate data with other function.
Re: how to remove special characters from oracle forms fields? [message #606702 is a reply to message #606690] Tue, 28 January 2014 04:27 Go to previous messageGo to next message
mughals_king
Messages: 351
Registered: January 2012
Location: pakistan
Senior Member
What "GAME" Does not make any sense @Sir littlefoot gave you 100% solution you can handle this by using REPLACE/REGEXP_REPLACE

Oracle REGEXP_REPLACE remove commas from string only if not enclosed in()'s

Oracle/PLSQL: REPLACE Function??

The Oracle/PLSQL REPLACE function replaces a sequence of characters in a string with another set of characters.

Example
REPLACE('123123Mughal', '123'); 

--------
Result

'Mughal'



Example

SQL> with t as
  2  (select 'Sir littlefoot|Mughal|King' col
  3  from dual union all
  4  select 'oracle|certified|professional'
  5   from dual
  6   )
  7   select
  8   regexp_replace(regexp_replace(col, '[|]',' '),'([0-9]) ([0-9])','\1|\2')
  9   col
 10   from t
 11  /

COL
---------------------------------------------------------------------------
Sir littlefoot Mughal King
oracle certified professional



http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm

https://community.oracle.com/thread/670970

Regard
Mughal


[Updated on: Tue, 28 January 2014 04:29]

Report message to a moderator

Re: how to remove special characters from oracle forms fields? [message #606704 is a reply to message #606701] Tue, 28 January 2014 04:29 Go to previous messageGo to next message
jineesh
Messages: 14
Registered: January 2014
Junior Member
I tried to remove '-' with this query am getting data .but, I can't use this function on form 6i


select regexp_replace('as345,df,3_df', '[,,_,]', '') text_string from dual;

Answer

as345df3df

Re: how to remove special characters from oracle forms fields? [message #606705 is a reply to message #606704] Tue, 28 January 2014 04:31 Go to previous messageGo to next message
jineesh
Messages: 14
Registered: January 2014
Junior Member

This is what "this is what am not playing game or what. " I replyed to "pablolee"
Re: how to remove special characters from oracle forms fields? [message #606706 is a reply to message #606705] Tue, 28 January 2014 04:45 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Forms 6i doesn't pre-dates regexp_replace so doesn't recognize it, that's what you get for running an old unsurported version of forms (time to upgrade to 11g).
You can either:
a) create a db function that does the regexp_replace and call that from the form.
b) make do with normal replace instead.
Re: how to remove special characters from oracle forms fields? [message #606710 is a reply to message #606706] Tue, 28 January 2014 05:48 Go to previous messageGo to next message
jineesh
Messages: 14
Registered: January 2014
Junior Member
thanks .I am planning to create Db Function.
Re: how to remove special characters from oracle forms fields? [message #606733 is a reply to message #606710] Tue, 28 January 2014 07:15 Go to previous messageGo to next message
mughals_king
Messages: 351
Registered: January 2012
Location: pakistan
Senior Member
Yes @cookiemonster is right try to create DB function i have acomplished this task many time ago i would give you some examples may be could help you


SQL> connect sys/manager@arla as sysdba
Connected.
SQL> CREATE OR REPLACE FUNCTION no_spec(p_row VARCHAR2) RETURN VARCHAR2 IS
  2       v_result VARCHAR2(4000) := '';
  3  BEGIN
  4       FOR i IN 1..LENGTH(p_row) LOOP
  5              IF SUBSTR(p_row,i,1) >= 48 AND SUBSTR(p_row,i,1) <= 122 THEN
  6                     v_result := SUBSTR(p_row,i,1);
  7              END IF;
  8       END LOOP;
  9  
 10       return v_result;
 11  
 12  END no_spec;
 13  /

Function created.
-----------

Now use it

SELECT no_spec(my_column) from my_table;



You could just use the TRANSLATE function:

PROCEDURE no_special_chars
IS
  ok_characters VARCHAR2(1000) :=
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz012456789., ';

  -- add to 'ok_characters' all the characters that are ok to keep...any characters NOT in this
  -- list will be removed through the use of the TRANSLATE function.

BEGIN
  UPDATE my_table
     SET column_name = TRANSLATE( column_name, ok_characters, ok_characters);
END;


FUNCTION no_special_chars( p_input VARCHAR2 )
RETURN VARCHAR2
IS
  ok_characters VARCHAR2(1000) :=
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz012456789., ';
  return_string VARCHAR2(4000);

BEGIN
  SELECT TRANSLATE( p_input, ok_characters, ok_characters) INTO return_string FROM dual;
  RETURN ( return_string );
END;  

FUNCTION no_special_chars( p_input VARCHAR2 )
RETURN VARCHAR2
IS
  ok_characters VARCHAR2(1000) :=
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz012456789., ';
  return_string VARCHAR2(4000);

BEGIN
  SELECT TRANSLATE( p_input, TRANSLATE( p_input, ok_characters, ok_characters), ok_characters )
     INTO return_string FROM dual;
  RETURN ( return_string );
END;  

Then you could:

SELECT no_special_chars( your_column) FROM your_table;


DECLARE
   str   VARCHAR2 (4000) := 'dd34g67,8;4,jr[7]9[5=-\],o;0';
   nbr   VARCHAR2 (4000);
   n     VARCHAR2 (1);
BEGIN
   FOR i IN 1 .. LENGTH (str)
   LOOP
      n := SUBSTR (str, i, 1);

      IF n IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
      THEN
         nbr := nbr || n;
      END IF;
   END LOOP;

   dbms_output.put_line (nbr);
END;
/


And this was my target when i was acomplished this task i don't exactly remember

Try this

Create this function by running this script:
-------
CREATE FUNCTION [DBO].[STRIPNONALPHACHARACTERS](@INPUT VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
    WHILE PATINDEX('%[^A-Z]%', @INPUT) > 0
        SET @INPUT = STUFF(@INPUT, PATINDEX('%[^A-Z]%', @INPUT), 1, '')
 
    RETURN @INPUT
END

--Check it with these
--Select dbo.StripNonAlphaCharacters('abc1234def5678ghi90jkl')
--Select dbo.StripNonAlphaCharacters('1Smith*45')
---------

Then run this script to create the tables, populate, and test:

---------
IF OBJECT_ID('#table1', 'U') IS NOT NULL
  DROP TABLE #table1
GO

CREATE TABLE dbo.#table1
(
      model char(10) NULL
)
GO


IF OBJECT_ID('dbo.#table12', 'U') IS NOT NULL
  DROP TABLE dbo.#table2
GO

CREATE TABLE dbo.#table2
(
      model char(10) NULL
)
GO

Insert into #table1 (model)
select 'ABC*1234'
union
select ('DEF*1234')
union all
select ('GHI*1234')
union all
select ('JKL*56789')
union all
select ('JKL*56789')
union all
select ('JKL*56789')

Insert into #table2 (model)
select ('ABC')
union all
select ('DEF')
union all
select ('GHI')
union all
select ('JKL')
union all
select ('JKL')
union all
select ('JKL')

--See that it does not match
select *
from #table1 t1, #table2 t2
where t1.model = t2.model

--See that it does match with function
select *
from #table1 t1, #table2 t2
where (dbo.StripNonAlphaCharacters(t1.model)) = t2.model
---------


Hope something you will get from this.

Regard
Mughal





Re: how to remove special characters from oracle forms fields? [message #606737 is a reply to message #606733] Tue, 28 January 2014 07:44 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
The first function doesn't work at all - you're missing two calls to the ascii function. If you add them it'll only return the last character in the string that matches.
The last example isn't oracle code.

And you should never, ever create objects as sys.
Re: how to remove special characters from oracle forms fields? [message #606738 is a reply to message #606737] Tue, 28 January 2014 08:11 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Swishhhh, swing and a miss.
Re: how to remove special characters from oracle forms fields? [message #606772 is a reply to message #606738] Wed, 29 January 2014 04:13 Go to previous messageGo to next message
mughals_king
Messages: 351
Registered: January 2012
Location: pakistan
Senior Member
Have you solved or still not or should i try to solve Cool just i try to use figure as you given your previous post like "9787-765675765,5645-654545656,5665465-65456456,67446466656'

just trying to resolve your issue thn i thinl i got it if you are still facing any problem thn let me know.

Example---in your case little things we have to change

  1  DECLARE
  2     str   VARCHAR2 (4000) := '"9787-765675765,5645-654545656,5665465-65456456,67446466656';
  3  BEGIN
  4     dbms_output.put_line (regexp_replace(str,'[^[:digit:]]',''));
  5* END;
SQL> /

9787765675765564565454565656654656545645667446466656

PL/SQL procedure successfully completed.



Regard
Mughal
Re: how to remove special characters from oracle forms fields? [message #606773 is a reply to message #606772] Wed, 29 January 2014 04:19 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Read over the thread again. Can you see what is wrong with the code that you have suggested?
Re: how to remove special characters from oracle forms fields? [message #606776 is a reply to message #606773] Wed, 29 January 2014 04:35 Go to previous message
jineesh
Messages: 14
Registered: January 2014
Junior Member
Thanks dear all Solved
Previous Topic: LOV {OK} Button Value Into Form
Next Topic: Query Only Mode at Menu Level
Goto Forum:
  


Current Time: Fri Apr 18 16:21:49 CDT 2014

Total time taken to generate the page: 0.10268 seconds