-1

I'm using oracle and hive db engine and supposed that in a varchar column (phone number), I only want to retrieve record with digit and hyphen '-' i.e. 03-1234 5678

But how if I want to retrieve if column has special chars (except hyphen) and alphabet using like or rlike.

i.e 03-ABC123$#45XYZ or 03-AB123 Y123#& (with space)

Thanks in advance!

Belle
  • 1
  • 1
  • 4
  • You've asked for two different things here. What type of phone numbers do you actually want to match? – Tim Biegeleisen Feb 10 '22 at 03:21
  • Hi @TimBiegeleisen. I want to only retrieve phone number with number and hyphen (03-12345 6789) only. Others should be invalid. but also if I want to do rlike, what is the reverse operation for it.. – Belle Feb 10 '22 at 04:03

1 Answers1

0

Using RLIKE we can try:

SELECT * FROM yourTable WHERE phone RLIKE '^[0-9 -]+$';

This will match only phone numbers consisting of digits, space, or hyphen.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360