March 17, 2016 at 9:46 am
trying to add a characteristic to our software where whenever we receive a car with a Make ('hisun', 'kymco', etc) it gets highlighted also some makes with specific models like 'Suzuki gs500'. My concern is what if there are spelling errors or indifferent spaces between them for example (Land Master, LandMaster) would not pop up in my query below.
If anyone knows what would be the best way to eliminate or even reduce the chances of those kind of errors. I am using the below query.
SELECT
CHAR_VALUE = CONVERT(BIT, (SELECT COUNT (asset_id)
FROM vehicle
WHERE (application_id=application_id and make in ('cfmoto','hisun','kubota','kymco','land master','massimo')
or (application_id=application_id and make like '%suzuki%' and model in ('b-King', 'gs500', 'bandit', 'gsx', 'Hayabusa')
or (application_id=application_id and make like '%c%a%n%a%m%' and ((model like '%o%u%t%l%a%n%d%e%r%') or (model like '%xmr650%') or (model like '%800%') or (model like '%1000%'))
)))))
March 17, 2016 at 10:37 am
The database is not smart, you will have to come up with something like predictive search, where the user will get a, hopefully short, list of spelling options based on the user input.
March 17, 2016 at 10:56 am
user does not have a option for selection, they can only type it in.
March 17, 2016 at 9:46 pm
SQLPain (3/17/2016)
user does not have a option for selection, they can only type it in.
There should be some form of validation on the front end to prevent this type of thing. Otherwise, the only thing you can do is do what you can and can the rest because even the best algorithms aren't going to be able to catch everything especially when you're looking for things like '%c%a%n%a%m%'. 😉
Personally, I'd light a fire under someone's keester to make the front-end do some validation or incorporate a table driven selection tree. There's real no excuse for bad data making it this close to the database.
--Jeff Moden
March 18, 2016 at 9:25 am
Thank you guyz!!
March 18, 2016 at 10:11 am
I agree with Jeff on this one. Trying to do this in sql is dealing with the symptom instead of the problem. In this case the problem is a poorly designed front end. I have never seen an application dealing with vehicles that allowed to just type in the make and model. This is typical situation where you have nested comboboxes. The first one you choose the make (Suzuki, Honda, etc..). When you chose an option in the makes combobox, the options of the models (Accord, Civic, etc...) combobox gets populated for the maker selected. This eliminates all the crazy validation and makes the application far more user friendly. You then just need one more combobox for year and you a perfectly validated and easy to use front end.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2016 at 11:03 am
I agree with all that has been said. But, maybe you could make it work implementing Full-Text Search? This is only if you already have problems with this data, if it's a development in progress, switch to Sean's recommendation.
March 18, 2016 at 8:17 pm
Luis Cazares (3/18/2016)
I agree with all that has been said. But, maybe you could make it work implementing Full-Text Search? This is only if you already have problems with this data, if it's a development in progress, switch to Sean's recommendation.
Just curious, Luis... have you ever setup FTS and done some performance testing with this type of thing compared to other methods?
--Jeff Moden
March 18, 2016 at 10:01 pm
Jeff Moden (3/18/2016)
Luis Cazares (3/18/2016)
I agree with all that has been said. But, maybe you could make it work implementing Full-Text Search? This is only if you already have problems with this data, if it's a development in progress, switch to Sean's recommendation.Just curious, Luis... have you ever setup FTS and done some performance testing with this type of thing compared to other methods?
No, that's why I mentioned maybe. It's supposed to be better, but we don't like suppositions, do we?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply