check format [message #659161] |
Sun, 08 January 2017 04:36 |
|
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 #659163 is a reply to message #659162] |
Sun, 08 January 2017 09:45 |
Solomon Yakobson
Messages: 3275 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 #659166 is a reply to message #659163] |
Sun, 08 January 2017 11:00 |
Solomon Yakobson
Messages: 3275 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.
|
|
|
|
|