SJGP Software

Sometimes our techie-brains convert a concept clearly articulated in plain English into unnecessarily mathematically expressed code. For example, "I want all records with an amount between 1000 and 1999" can become "x >= 1000 AND x <= 1999" But modern RDBMS database systems give us an English equivalent that saves translating the concept "between" into "greater than or equal to this, and less than or equal to that." It's the BETWEEN function, and it is the same across SQL Server, PostgreSQL, MySQL, and Oracle. Maybe others, too, but those are the ones that I have used in the past. BETWEEN is of the form: MyTestValue BETWEEN StartValue AND EndValue It is an inclusive comparison, equivalent to using >= and <= So these generate the same results: SELECT * FROM MyTable WHERE MyValue BETWEEN 100 AND 1000 vs SELECT * FROM MyTable WHERE MyValue >= 100 AND MyValue <= 1000 What are the advantages of BETWEEN? It's a...

When selecting data in SQL Server, the WHERE-clause has, in my experience, ignored the case and done a case-insensitive comparison. For example, my Individuals table may have a mix of upper and lower cases, depending on how people entered their names: SELECT firstname, lastname  FROM Individuals WHERE lastname = 'Timmins' firstname lastname --------- -------- Tammy TIMMINS Tellie timmins Tommy TimMinS Tubby Timmins My query selected all four people with the last name "Timmins" without caring if it was all capitals, all lower case, or a mixture. I rely on that behavior all the time. But once in a while, we need to do a case-sensitive query. How can we, for example, find Tommy TimMinS and correct it to Tommy Timmins?