Home » SQL & PL/SQL » SQL & PL/SQL » check format (10g)
check format [message #659161] Sun, 08 January 2017 04:36 Go to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
Hello experts,

I have one field where user is entering the dimensions for item code like there are two different dimensions one for section and plates.
For section type the dimension will be in length and user will enter input as 12M
For plate type the dimension will be in lengthXWidth format user will enter input as 2MX12M,

Examples

12M
2MX12M
2.5MX12M
3MX30M

Is there a way to control this through validation like if he enters 2X12M system should stop him
or he enter 12000 system should stop it.
Valid format are like

12M
2MX12M.

Regards

Re: check format [message #659162 is a reply to message #659161] Sun, 08 January 2017 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> with
  2    data as (
  3      select '12M' value from dual union all
  4      select '2MX12M' value from dual union all
  5      select '2.5MX12M' value from dual union all
  6      select '3MX30M' value from dual union all
  7      select '12' value from dual union all
  8      select '2X12M' value from dual union all
  9      select '2.5MX12' value from dual union all
 10      select '3X30' value from dual
 11    )
 12  select value,
 13         case
 14           when regexp_like(value,'^\d+(\.\d+)?M(X\d+(\.\d+)?+M)?$') then 'CORRECT'
 15           else 'WRONG'
 16         end status
 17  from data
 18  /
VALUE    STATUS
-------- -------
12M      CORRECT
2MX12M   CORRECT
2.5MX12M CORRECT
3MX30M   CORRECT
12       WRONG
2X12M    WRONG
2.5MX12  WRONG
3X30     WRONG
Re: check format [message #659163 is a reply to message #659162] Sun, 08 January 2017 09:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
Registered: January 2010
Location: Connecticut, USA
Senior Member
Question is if whole part must always be present and something like '.2MX.12M' should be considered wrong so user must enter '0.2MX0.12M'. Otherwise:

with
  data as (
    select '.12M' value from dual union all
    select '.2MX.12M' value from dual union all
    select '2MX.12M' value from dual union all
    select '.2MX12M' value from dual
  )
select value,
       case
         when regexp_like(value,'^\d+(\.\d+)?M(X\d+(\.\d+)?+M)?$') then 'CORRECT'
         else 'WRONG'
       end status
from data
/

VALUE    STATUS
-------- -------
.12M     WRONG
.2MX.12M WRONG
2MX.12M  WRONG
.2MX12M  WRONG

SQL>


Also, I'd look into creating check constraint.

SY.
Re: check format [message #659164 is a reply to message #659163] Sun, 08 January 2017 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, something I didn't think about as we always have a whole part in my country. Smile
As well as a decimal point without decimal part like "2.M" which is returned as WRONG in my query.

Re: check format [message #659166 is a reply to message #659163] Sun, 08 January 2017 11:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
Registered: January 2010
Location: Connecticut, USA
Senior Member
Solomon Yakobson wrote on Sun, 08 January 2017 10:45

Also, I'd look into creating check constraint.
I stand corrected. Table design is just wrong. OP must look into changing that column into two: length and width. Then table would be normalized and whole issue would be avoided.

SY.
Re: check format [message #659169 is a reply to message #659166] Sun, 08 January 2017 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

@OP, read Normalization.

Re: check format [message #659170 is a reply to message #659169] Sun, 08 January 2017 12:33 Go to previous message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
thanks Solomon and Michael.
Previous Topic: Getting ORA-01422: exact fetch returns more than requested number of rows
Next Topic: GROUP BY COST CENTER
Goto Forum:
  


Current Time: Sat Jan 20 06:02:55 CST 2018

Total time taken to generate the page: 0.02212 seconds