We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
Anyone good with SQL?



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.
Comments
-
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.1 -
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_AS1
-
^^^ 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!1
-
Another vote to change case using upper or lower and don’t forget to do the same for the description1
-
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.
0 -
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.I’m a Forum Ambassador and I support the Forum Team on the In My Home MoneySaving, Energy and Techie Stuff boards. If you need any help on these boards, do let me know. Please note that Ambassadors are not moderators. Any posts you spot in breach of the Forum Rules should be reported via the report button, or by emailing forumteam@moneysavingexpert.com.
All views are my own and not the official line of MoneySavingExpert.
1 -
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.
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.0 -
You could always use some regular expressions to ignore the apostrophes if they become a problem
0 -
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.
No reliance should be placed on the above! Absolutely none, do you hear?2 -
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'?No reliance should be placed on the above! Absolutely none, do you hear?1
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 350.4K Banking & Borrowing
- 252.9K Reduce Debt & Boost Income
- 453.3K Spending & Discounts
- 243.4K Work, Benefits & Business
- 598K Mortgages, Homes & Bills
- 176.6K Life & Family
- 256.5K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards