Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    May 2004
    Location
    Minneapolis, MN, USA
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Treat certain characters in database fields as nonexistant in searches

    This client wants hyphens, underscores and both slashes to be considered as if they aren't there when users do searches on their inventory, but they still want those characters to be displayed as the actual product name when the results come up.

    So, the easy solution of changing the code that inputs the data into the database to strip out those characters ahead of time is out of the question. This leaves me back at a bit of a block, since I can't really think of a way to achieve this desired result then. One of the only seemingly feasible solutions I've got in my head is to grab ALL records and throw them into an array, stripping out the characters along the way, but that would just be a ridiculous amount of looping, querying and excessive use of resources, I think.

    The language I'm working with is ColdFusion, but specific language syntax aside, I'm still trying to figure out how this might be done conceptually. I'm not sure it can be, but I thought I'd see what y'all thought. Thoughts?

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    just create an extra column where you store the value after removing the hyphens etc.

    So you'll have the original value and the stripped one. You then run your search against the stripped one, but only select the original column.

    but of redundant data but it wount have any impact on performance. Will just take up a bit more space.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    Regular Coder
    Join Date
    May 2004
    Location
    Minneapolis, MN, USA
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sure, and that is in fact what I ended up doing... I was just being the idealist that I am and trying to figure how it might be done without the redundancy.

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ]|V|[agnus
    Sure, and that is in fact what I ended up doing... I was just being the idealist that I am and trying to figure how it might be done without the redundancy.
    A true idealist should always put runtimeresources and processingtime first (certainly if the downsides in terms of extra required space and needing to always insert/update 2 columns instead of 1 are so minor)

    I think that when it comes to searches and filtering out records, that your db should be optimised for that because retrieving large resultsets and then processing them in your applicationlayer always consumes much more resources then if you can do it with one simple query.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #5
    Regular Coder
    Join Date
    May 2004
    Location
    Minneapolis, MN, USA
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hmm, perhaps, but I kinda feel like this sort of solution is, in a conceptual way, akin to using tables for layout. It's a sloppy workaround of sorts in the absence of the proper tools to do it "right".

    Now, that doesn't mean it shouldn't be done this way. Obviously, I've come up with nothing better, but you see my point I hope.

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ]|V|[agnus
    Hmm, perhaps, but I kinda feel like this sort of solution is, in a conceptual way, akin to using tables for layout. It's a sloppy workaround of sorts in the absence of the proper tools to do it "right".

    Now, that doesn't mean it shouldn't be done this way. Obviously, I've come up with nothing better, but you see my point I hope.
    No i don't.

    As with many db-processes, you need to make a decission between having a completely normalised db without any update anomalys and a performant system.
    Since your db powers a website where responsetimes need to be kept as low as possible, there is no reason why you shouldn't choose for the most performant sollution. Especially not since the downsides are so minor. (You will however no longer be able to where your "My db is fully normalised" T-shirt)
    There are probably ways to do some sort of regex-search or replace on the values you then match against, but this will all consume more resources then simply having a cleaned value in another column.

    Your comparison to using tables for layoutpurposes is not valid. Tables generally require more server side code to create them, produce more sourcecode, usualy require spacergifs etc. As a general rule, using tables for layout will consume more runtime and bandwidth. So even from a purely performance and resources standpoint, tables should not be used for layoutpurposes and in quite a few cases, even not for tabular data.

    (luckely for us, machines have become so powerfull and RAM and bandwidth has become so cheap, that we don't realy notice most resource spilling code and designs)
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •