query the characters in any order?

Anon

If the primary key in my database is a series of 5 letters, I would like to be able to retrieve that record by performing a query with the letters in ANY ORDER. For example:

The primary key for record #1 = "ABCDE"

I want to be able to search for that record using any combination of those 5 letters. So, I could retrieve the record if I searched using "EDBCA", or "BADCE" or "DCBEA" etc etc

Is there a REGEXP or something where I can perform this operation? I have played around a little bit with regexp, but I haven't really found anything.

Thanks

Jon

Anon

Probably could be done with a regexp. But if the primary key is an unordered set of five letters, why not sort them before storing in the db? Then sort the search string before running the query. That way you will always have the key.

If the order of the letters in the keys is significant I think you have a design problem; essentially you have tried to put an array into a table row, which almost always gets you into trouble like this.

Anon

If you're using say postgres, you can just use a CASE statement that maps each letter to an order num and then sort by the CASE statement. This is similar to the Oracle DECODE statement.

Alternatively, you can use UNION ALL to join together 5 separate queries for each letter into one query.

Chris

Anon

Excellent! That did the trick. I didn't even think to sort the letters when I put them in the database. Thank you for the help!

Jon

Anon

I originally tried something similar to that, but the code go unnecessarily comlicated for me to work through. If all else fails, I will try your suggestion. Thank you!

Jon