Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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