Typing Spelling error check

Question

Typing Spelling error check

SQLPain

Hall of Fame

Points: 3522
More actions
March 17, 2016 at 9:46 am

#309129

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%'))
)))))

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

The Danish Dynamo SSC-Addicted Points: 464 More actions · Answer 1

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.

SQLPain Hall of Fame Points: 3522 More actions · Answer 2

user does not have a option for selection, they can only type it in.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLPain Hall of Fame Points: 3522 More actions · Answer 4

SQLPain

Hall of Fame

Points: 3522

March 18, 2016 at 9:25 am

#1866169

Thank you guyz!!

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

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?