mysql - Regular expression to exclude a particular number -

i have model called department.

    class department < activerecord::base         # associations       belongs_to :client       belongs_to :facility        scope :facility_departments, ->(facility_id) {                                    where("facility_id = ? ", facility_id)}        scope :matching_departments, ->(facility_id, identifier) {facility_departments(facility_id).where("  ? regexp reg_exp ", identifier.to_s).order("weight desc") }      end 

• in departments table have facility_id, , regular expression column.
• i have employees in application, each 1 having department id. in order identify employees department, i'm using scope matching_departments in department model (see above).
• each employee has got identifier too.
• all employees having numeric identifier , identifier length = 9 , except 9 zeros (000000000) - should map department 1.

so should regular expression department-1 in departments table? updated (^[0-9]{9,}$) - matching numeric identifiers length 9. how can exclude 9 zeros?

it isn't pretty, should work simplest regex flavor. make 9 separate tests checks non 0 digit in each position:

^(?:[1-9]\d{8}|\d[1-9]\d{7}|\d{2}[1-9]\d{6}|\d{3}[1-9]\d{5}|\d{4}[1-9]\d{4}|\d{5}[1-9]\d{3}|\d{6}[1-9]\d{2}|\d{7}[1-9]\d|\d{8}[1-9])$ 

first test 1-9 in first position followed 8 digits. next digit followed 1-9 in turn followed 7 digits. , on...

if regex flavor doesn't support digit character class, replace \d [0-9].

hope works you.

regards

edit: might non capturing groups aren't supported replace ordinary capture group:

^([1-9][0-9]{8}|[0-9][1-9][0-9]{7}|[0-9]{2}[1-9][0-9]{6}|[0-9]{3}[1-9][0-9]{5}|[0-9]{4}[1-9][0-9]{4}|[0-9]{5}[1-9][0-9]{3}|[0-9]{6}[1-9][0-9]{2}|[0-9]{7}[1-9][0-9]|[0-9]{8}[1-9])$