📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!

Anyone good with SQL?

JohnB47
JohnB47 Posts: 2,665 Forumite
Part of the Furniture 1,000 Posts Name Dropper

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.


«134

Comments

  • Lorian
    Lorian Posts: 6,185 Forumite
    Part of the Furniture 1,000 Posts Name Dropper Photogenic
    edited 5 January at 2:23PM
    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
    teaselMay Posts: 659 Forumite
    500 Posts First Anniversary Name Dropper
    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
    Heedtheadvice Posts: 2,750 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    ^^^ 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
    Tony5896 Posts: 74 Forumite
    10 Posts First Anniversary
    Another vote to change case using upper or lower and don’t forget to do the same for the description 
  • JohnB47
    JohnB47 Posts: 2,665 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    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
    victor2 Posts: 8,076 Ambassador
    Part of the Furniture 1,000 Posts Name Dropper
    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.

  • JohnB47
    JohnB47 Posts: 2,665 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    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
    teaselMay Posts: 659 Forumite
    500 Posts First Anniversary Name Dropper
    You could always use some regular expressions to ignore the apostrophes if they become a problem

  • GDB2222
    GDB2222 Posts: 26,052 Forumite
    Part of the Furniture 10,000 Posts Photogenic Name Dropper
    edited 5 January at 5:36PM
    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?
  • GDB2222
    GDB2222 Posts: 26,052 Forumite
    Part of the Furniture 10,000 Posts Photogenic Name Dropper
    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?
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

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

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.