I need a regexp_like to validate only letters D or T, numbers and Pipe

Question

I would like to use Oracle's regexp_like to only allow the following:

• Letters D or T
• Any number including decimal and negative
• special character Pipe |

Really appreciate your input :)

Following are valid lines

D|1005433608369|202311220039403004157|2023112200394030041570002|0|-53|4157|20231122|30|394|820|2|0|0|0|0
D|1006233842021|202311220039403004160|2023112200394030041600001|1|0|4160|20231122|30|394|868|1|46002136|98|35|0
D|1006233842021|202311220039403004160|2023112200394030041600002|0|-63|4160|20231122|30|394|820|2|0|0|0|0
D|1004420222359|202311220039403004161|2023112200394030041610001|1|0|4161|20231122|30|394|868|1|45941436|108|54|0
D|1004420222359|202311220039403004161|2023112200394030041610002|0|-54|4161|20231122|30|394|820|2|0|0|0|0
D|1005557156227|202311220039403004187|2023112200394030041870001|1|0|4187|20231122|30|394|868|1|46189226|98|2

Following is an invalid line

T|2401|20231124|202311240240138702117|1015453705|5|193|00 A+%?Pu?Z?3<???

Answer 1

Based on your actual sample data which implies that D or T would only appear at the very beginning of the line, followed by pipe separated numbers, the following query should work:

SELECT val
FROM yourTable
WHERE REGEXP_LIKE(val, '^[DT](\|-?[0-9]+(\.[0-9]+)?)+$')

If D or T could really appear anywhere in the pipe separated string, then we would have to do more work:

^(-?[0-9]+(\.[0-9]+)?|[DT])(\|(-?[0-9]+(\.[0-9]+)?\|[DT]))*$

Oracle query:

SELECT val
FROM yourTable
WHERE REGEXP_LIKE(val, '^(-?[0-9]+(\.[0-9]+)?|[DT])(\|(-?[0-9]+(\.[0-9]+)?|[DT]))*$');

Demo

This regex pattern says to match:

• ^ from the start of the line
• (
  • -? optional leading negative sign
  • [0-9]+(\.[0-9]+)? an integer or decimal
  • | OR
  • [DT] the letters D or T
• )
• (
  • \| pipe separator
  • (
    • -? optional leading negative sign
    • [0-9]+(\.[0-9]+)? an integer or decimal
    • \| OR
    • [DT] the letters D or T
  • )
• )* pipe then number/letter expression zero or more following times
• $ end of the line