I want to check the length of the value after removing spaces and hyphens and save the value with no spaces but with hyphens in database.

My logic goes as follows:

IF(LENGTH(REPLACE(REGEXP_REPLACE(myValue,'[[:space:]]*',''),'-','')=13)
THEN
 --myValue := REGEXP_REPLACE(myValue,'[[:space:]]*',''); 
 --Insert...myValue
END IF;

But the if condition is failing and the data is not getting saved. Please help me to identify what I am missing.

CodePudding user response：

I don't think you need regular expressions for that.

For such a sample data, query returns following results:

SQL> with sample_data (id, col) as
  2    (select 1, 'abc def-ghi123 4' from dual union all
  3     select 2, '12  -xyz--238-62' from dual)
  4  select id,
  5         col,
  6         replace(replace(col, ' '), '-') val,
  7         --
  8         case when length(replace(replace(col, ' '), '-')) = 13 then 'Y' else 'N' end cb_insert,
  9         replace(col, ' ') val_to_insert
 10  from sample_data;

        ID COL              VAL              C VAL_TO_INSERT
---------- ---------------- ---------------- - ----------------
         1 abc def-ghi123 4 abcdefghi1234    Y abcdef-ghi1234   --> insert
         2 12  -xyz--238-62 12xyz23862       N 12-xyz--238-62   --> don't insert

SQL>

• val is the string whose spaces and hyphens are removed
• cb_insert: if Y, that value should be inserted as the length of a string with removed spaces and hyphens is equal to 13
• val_to_insert: source string with spaces being removed

So, for target table:

SQL> create table test (id number, value varchar2(20));

Table created.

insert query looks like this (reusing the above query):

SQL> insert into test (id, value)
  2    with
  3    sample_data (id, col) as
  4      (select 1, 'abc def-ghi123 4' from dual union all
  5       select 2, '12  -xyz--238-62' from dual
  6      ),
  7    temp as
  8      (select id,
  9              col,
 10              replace(replace(col, ' '), '-') val,
 11              --
 12              case when length(replace(replace(col, ' '), '-')) = 13 then 'Y' else 'N' end cb_insert,
 13              replace(col, ' ') val_to_insert
 14       from sample_data
 15      )
 16    select id, val_to_insert
 17    from temp
 18    where cb_insert = 'Y';

1 row created.

Result:

SQL> select * From test;

        ID VALUE
---------- --------------------
         1 abcdef-ghi1234

SQL>

CodePudding user response：

But the if condition is failing and the data is not getting saved. Please help me to identify what I am missing.

You have two potential problems:

1. If your string is entirely white-space characters or hyphens then they will all get replaced and you will have an empty string. In Oracle, an empty string is the same as NULL and LENGTH(NULL) outputs NULL and NULL=<anything> is always false. This may not be a problem but it may be causing unexpected results.
2. You check that the length without spaces or hyphens is 13 and then you insert the data without spaces but with the hyphens. If you have a column that can store 13 (or even 16) characters and you check that the input will fit without hyphens but then you allow an unknown number of hyphen characters then your INSERT will fail as the value will be too large for the column.

DECLARE
  myValue VARCHAR2(50);
  mv_no_spaces VARCHAR2(50);
BEGIN
  myValue := '   abcde-fgh-ijk----lm  ' || CHR(9) || CHR(10) || CHR(13) || '   ';
  mv_no_spaces := REGEXP_REPLACE(myValue,'[[:space:]]*');

  IF  LENGTH(REPLACE(mv_no_spaces,'-','')) = 13
  AND LENGTH(mv_no_spaces) <= 20 -- or whatever your column size is.
  THEN
    INSERT INTO your_table (your_column) VALUES (mv_no_spaces);
  END IF;
END;
/

db<>fiddle here