Anyone good with SQL?

JohnB47

Just wondering if SQL can be made to ignore character case when selecting a field dependant on content.

I have some SQL that I use to allocate a ‘Type’ number in a table in LibraOffice Base. This looks at a field called ‘Description’ and allocates a Type number accordingly, like this:

UPDATE "tblSpend"

SET "Type" = '4'

WHERE ("Description" Like '%Freds Groceries%')

The trouble is that the case (upper/lower) of the entries in field ‘Description’ keep changing (this originates in a downloaded spreadsheet and is out of my control). Sometimes “Description” would show as Freds Groceries, other times FREDS GROCERIES and even freds groceries.

I do change my code, for example to this, but it becomes annoying to keep changing it:

UPDATE "tblSpend"

SET "Type" = '4'

WHERE ("Description" Like '%Freds Groceries%' OR "Description" Like '%FREDS GROCERIES%')

So, is there a way to change my code so that it recognises all types of entry, whether it be all lower, all upper or mixed case, as long as the field has all the right letters in the right order, as Eric Morecambe might not say?

Thanks.

Lorian

It's decades since I was a SQL programmer and rightly or wrongly I used to put upper() around both terms items in the where clause to make the match case insensitive.

Where upper(data) like upper('whatever im looking for')

Obviously you could just write the second one in upper case.

teaselMay

Inherently it's usually case insensitive. I'm rusty too but yep if yours isn't working insensitively as Lorian says changing case of everything should work, sometimes need to do it as both UPPER and LOWER to ensure correct recognition, or explicitly tell it to use a case insensitive collation using COLLATE SQL_Latin1_General_CP1_CI_AS 

Heedtheadvice

^^^ me too.

Also note 'LOWER' function does the reverse....

and 'SOUNDEX' can be useful to use how a text string sounds like. Often most useful to flag up differing spellings of same ( or similar!!) words but use with care!

Tony5896

Another vote to change case using upper or lower and don’t forget to do the same for the description 

JohnB47

Thanks for the quick replies folks. Obviously I'll play around with those suggestions but I need to check my understanding.

A couple of you have referred to 'changing' the case and "Another vote to change case using upper or lower." I don't actually want to change the entries in the "Description" field. I want the SQL to inspect and find those records where that field has, say Fred, or FRED or fred in them and then carry out a procedure accordingly.

Anyway, are you saying that this code would work (given my simple example):

UPDATE "tblSpend"

SET "Type" = '4'

WHERE (upper("Description") Like upper('%Freds Groceries%'))

I can't quite see why that would do what I want. But then what do I know! Thanks again.

victor2

JohnB47 said:

Thanks for the quick replies folks. Obviously I'll play around with those suggestions but I need to check my understanding.

A couple of you have referred to 'changing' the case and "Another vote to change case using upper or lower." I don't actually want to change the entries in the "Description" field. I want the SQL to inspect and find those records where that field has, say Fred, or FRED or fred in them and then carry out a procedure accordingly.

Anyway, are you saying that this code would work (given my simple example):

UPDATE "tblSpend"

SET "Type" = '4'

WHERE (upper("Description") Like upper('%Freds Groceries%'))

I can't quite see why that would do what I want. But then what do I know! Thanks again.

Many databases support case insensitive field, but in the absence of that, the suggested fixes will do the job...

WHERE (upper("Description") Like upper('%Freds Groceries%'))

is saying compare the upper case equivalent of the description field to the upper case equivalent of the text given.

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

would have the same effect. Neither actually alter the database content.

Of course if someone enters Fred's Groceries into the database, that's another story.

JohnB47

victor2 said:

JohnB47 said:

Thanks for the quick replies folks. Obviously I'll play around with those suggestions but I need to check my understanding.

A couple of you have referred to 'changing' the case and "Another vote to change case using upper or lower." I don't actually want to change the entries in the "Description" field. I want the SQL to inspect and find those records where that field has, say Fred, or FRED or fred in them and then carry out a procedure accordingly.

Anyway, are you saying that this code would work (given my simple example):

UPDATE "tblSpend"

SET "Type" = '4'

WHERE (upper("Description") Like upper('%Freds Groceries%'))

I can't quite see why that would do what I want. But then what do I know! Thanks again.

Many databases support case insensitive field, but in the absence of that, the suggested fixes will do the job...

WHERE (upper("Description") Like upper('%Freds Groceries%'))

is saying compare the upper case equivalent of the description field to the upper case equivalent of the text given.

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

would have the same effect. Neither actually alter the database content.

Of course if someone enters Fred's Groceries into the database, that's another story.

OK, I see now. Thanks for clarifying - I'll have a play with that.

Oh and yes, I've come across examples like Fred's. I discovered that I need to get the code to look for Fred''s in order for it to see those type of entries.

Thanks everyone.

teaselMay

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

GDB2222

Going off at a tangent, I copied the whole of the original question into ChatGPT. It came up with the solution suggested here. The advantage for the OP would have been that the whole process took 10 seconds. 

I've nothing against having a chat on the internet, but it seems that AI is also a good approach.

GDB2222

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'?