I have a column named parcel_number of varchar type. It contains values like ab-cd-ef and uvwxyz. A search for abcdef or uv-wx-yz does not return those records.

How should I write the query to meet this requirement?


This situation seems to be the result of a bad design decision.

As this column is not storing human readable content, but is to be used as an identifier, the content of this column should have a uniform structure, either with or without the dashes, but not mixing them! You'd also have a hard time having a UNIQUE constraint on this column...

I'd recommend stripping the dashes from the table:

UPDATE table SET parcel_number = replace(parcel_number,'-', '');

MySQL replace reference

Then you only have to strip the dashes from the the search string too. I assume ab-cd-ef is the same as abcdef. Do note however that doing so means a-bcd-ef and ab-cd-ef will be treated as being the same.

  • Why would you expect a varchar having structure in general? Regarding the actual question you may be right, but the wording here is too generalized, I think. – dezso Oct 17 '12 at 9:56
  • @dezso you are right, I tried to edit the answer, but this is not as easy to reflect the change you suggested as it first seemed to be... I hop the meaning got through. Did I succeed? – ppeterka Oct 17 '12 at 10:32
  • You did! Worth a +1 now. – dezso Oct 17 '12 at 10:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.