Anyone good with SQL?

JohnB47

GDB2222 said:

Incidentally, I asked ChatGPT your question about the apostophe (what I wrote was just: Fred's Groceries how best to deal with this as the data?) and it suggested:

WHERE LOWER(REPLACE("Description", '''', '')) LIKE '%freds groceries%';

I don't use SQL, but it's pretty obvious that that just changes the description to lower case and replaces the apostophe with nothing. 

I'm not sure whether '''' is right, or it needs an extra'? 

Thanks. Interesting stuff but a bit beyond me. I prefer a more human interaction - assuming that everyone who replies is actually human. 

teaselMay

My computer tells me I am regularly

JohnB47

I think I'll be onboard with AI in general when it can remind me why I went upstairs.

Heedtheadvice

Fred's sounds like Freds does it not?

...SOUNDEX......?

To cater for all similar text with punctuation by specific code, you will have to use specific code for each possible case.

Consider Fred's, Freds, Fred's. Fred's!, Fred"s @Freds Freds_

etc.

Ergates

GDB2222 said:

Incidentally, I asked ChatGPT your question about the apostophe (what I wrote was just: Fred's Groceries how best to deal with this as the data?) and it suggested:

WHERE LOWER(REPLACE("Description", '''', '')) LIKE '%freds groceries%';

I don't use SQL, but it's pretty obvious that that just changes the description to lower case and replaces the apostophe with nothing. 

I'm not sure whether '''' is right, or it needs an extra'? 

WHERE (upper("Description") Like ('%FRED%GROCERIES%'))

would match "Freds Groceries" and "Fred's Groceries" but also "Fred Groceries" and "Fred should really go and buy some Groceries".  So simpler (in that it uses fewer functions) but you have to know your data.

onomatopoeia99

teaselMay said:

You could always use some regular expressions to ignore the apostrophes if they become a problem

Confucius says: "Man who try to solve problem with regular expression now man with two problems"

flaneurs_lobster

Old programmer in me is shouting "get the input data validation tightened up".

Makes this type of problem unnecessarily hard if the conditional tests can have any old rubbish thrown at them. 

victor2

flaneurs_lobster said:

Old programmer in me is shouting "get the input data validation tightened up".

Makes this type of problem unnecessarily hard if the conditional tests can have any old rubbish thrown at them. 

But anyone who's developed any software knows there is nothing like a user to test it in ways you had never envisaged!

Ergates

victor2 said:

flaneurs_lobster said:

Old programmer in me is shouting "get the input data validation tightened up".

Makes this type of problem unnecessarily hard if the conditional tests can have any old rubbish thrown at them. 

But anyone who's developed any software knows there is nothing like a user to test it in ways you had never envisaged!

As we used to say:  No data load survives first contact with the user.

Ergates

flaneurs_lobster said:

Old programmer in me is shouting "get the input data validation tightened up".

Makes this type of problem unnecessarily hard if the conditional tests can have any old rubbish thrown at them. 

You can have the source system fixed yesterday, and you can have the source system fixed tomorrow, but you can never have the source system fixed today.