Regular Expression

Question

I have two ways to check a string for a special characters in 11g

1 ) Using SELECT

V_CNT_QRY := ' SELECT LENGTH(TRIM(TRANSLATE(:1,
                                             ''
                                             abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 '',
                                             '' '')))
                  from dual ';
EXECUTE IMMEDIATE  V_CNT_QRY INTO V_CNT USING V_COLUMN_DATA;

2) Using a procedure

CREATE OR REPLACE PROCEDURE Validate_Inputstring(input_String IN VARCHAR2) AS
BEGIN
  IF REGEXP_LIKE(input_String, '^[A-Z0-9a-z]*$') THEN
    DBMS_OUTPUT.PUT_LINE('U have entered alphanumeric chars--->' ||
                         input_String);
  ELSE
    DBMS_OUTPUT.PUT_LINE('U NOT have entered alphanumeric chars---->' ||
                         input_String);
  END IF;
END;

Please suggest a better way

What I mean is: there's no need to type in the translate call every time you need it. You can just create a function for it and hide all the ugly details. — bart
– bart, Commented Mar 25, 2011 at 23:05

Guillem Vicens · Accepted Answer · 2011-03-25 10:58:32Z

2

Supposing you want to check if the string contains only ASCII characters, you can use this:

Select 'No'
  From dual
 Where LENGTH(ASCIISTR(:input_String)) != LENGTH(:input_String)
 Union
Select 'Yes'
  From dual
 Where LENGTH(ASCIISTR(:input_String)) = LENGTH(:input_String)

HTH

answered Mar 25, 2011 at 10:58

Guillem Vicens

4,0061 gold badge33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alessmar · Accepted Answer · 2011-03-25 17:00:44Z

2

I would use the regexp_replace function in the following way:

select 'You have'|| decode(regexp_replace (:txt,'^[A-Z0-9a-z]*$', '1'), '1', ' ', ' not ')  || 'entered a string composed only by alphanum characters' from dual;

The bind variable txt contains the string to validate.

answered Mar 25, 2011 at 17:00

alessmar

4,7278 gold badges46 silver badges56 bronze badges

Comments

tbone · Accepted Answer · 2011-03-25 19:52:50Z

1

create table testx
( val1 varchar2(50));

insert into testx (val1) values ('Some ' || chr(9) || 'bad string');
insert into testx (val1) values ('Some nice string with 123 numbers');
commit;

select val1, regexp_instr(val1, '[^[:alnum:] ]') from testx;

The select will return 0 if string contains only alphanumeric or spaces, else > 0.

So, you can add a where clause to grab just the "bad" strings:

select val1 from testx where regexp_instr(val1, '[^[:alnum:] ]') > 0;

Of course you can modify this as needed depending on your definition of "special characters".

EDIT: if you don't care about flagging punctuation, try:

select val1 from testx where regexp_instr(val1, '[^[:alnum:] [:punct:]]') > 0;

again, modify as needed to your definition

edited Mar 25, 2011 at 19:52

answered Mar 25, 2011 at 19:34

tbone

15.5k3 gold badges37 silver badges42 bronze badges

Collectives™ on Stack Overflow

Regular Expression

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related