SQL Aggregate Functions

SQL Aggregate functions return a single value, using values in a table column. Below is a list of SQL aggregate functions and how to use them. AVG() Function The AVG() function returns the average value of a numeric column.

view plain print about
1SELECT AVG(column_name) FROM table_name

[More]

SQL Server Date Functions

In my quest to improve my SQL skills I wanted to get used to some of the date functions that can be used in SQL Server. year(), month(), day() Using the following SELECT statement I can extract the month from the date of birth field. I can then order the results by this data in order to get a month by month view of birthdays. The same could happen with year() and day()

view plain print about
1SELECT firstname, surname, month(dob) AS birthMonth, dob FROM yourTBL ORDER BY birthMonth
The results

[More]

SQL Wildcards

Ever since I started with SQL i've know about the '%' wildcard. Recently i've had this desire to improve my SQL skills and I found a few new wildcards which are outlined below. % Wildcard Using the following SELECT statement I can return all players who's firstname start's with an 'A' followed by zero or more characters.

view plain print about
1SELECT * FROM yourTBL WHERE firstname LIKE 'a%'
The results

[More]

Escaping underscores in MS SQL

Today I had to create a query to find all rows from a table in MS SQL Server where a certain column contained an underscore (_) charater. I was not aware but the '_' charater is just like the '%' character, a wildcard, I needed an escape sequence in order to make my query work. After a quick google search I found what seems to be a Microsoft-specific solution. You can use an escape character in your 'where' clause, then define that character at the end of your query by adding {escape '[your character]'} like this:

view plain print about
1<cfquery name="MyQuery" datasource="#Application.DSN#"> SELECT MyColumn FROM MyTable WHERE MyColumn like '%\_%' {escape '\'} </cfquery>

BlogCFC was created by Raymond Camden. This blog is running version 5.9.5.004. Contact Blog Owner