CODEWITHSUNDEEP
regexoracle11g
Khristian Liahut
Khristian Liahut

Reputation: 305

regular expression where the value must contain Letters, blank space and numbers

How to get a regular expression to define if the value contain Letters, blank space and numbers.

NOTE: The value must contain letters, numbers and blank space.

eg.

Maalderijstraat 45            Valid
Tronestraat                   Not Valid
Tronesttart45                 Not Valid
123456                        Not Valid

I' using this query:

select a.street
from clients a 
where REGEXP_LIKE (a.street,'^[a-zA-Z0-9]*$')

Upvotes: 0

Views: 115

Answers (2)

vks
vks

Reputation: 67968

  (?=.*?[ ].*?)(?=.*?[a-zA-Z]+.*?)(?=.*?[0-9]+.*?)(.*)

Look at the demo.

http://regex101.com/r/nW8dX7/2

Upvotes: 1

asontu
asontu

Reputation: 4659

You're regex doesn't contain any spaces and if you put them all in the same "class" (the thing between []) then only 1 of them has to match, so if every character matches a-z then it matches even though there wasn't a number in it.

Again I doubt you need regex for this:

select a.street
from clients a 
where a.street like '[A-Za-z]% %[0-9]'

But I suppose you could:

select a.street
from clients a 
where REGEXP_LIKE (a.street,'^[a-zA-Z ]+ [0-9]+$')

Upvotes: 1

Related Questions