Needing to change or replace a text, a word or phrase, within a VARCHAR column is very easy using the replace function.

This example changes every occurrence of the year "2018" with "2019". We are updating answers to the next year in an FAQ table.

First, how do you identify which columns contain "2018" or our search phrase?

Next we change or replace every occurrence of our search phrase with our new phrase or text.

How easy is that?

Just a reminder that this specific SQL is for VARCHAR only. TEXT and NTEXT fields will have to be CAST to VARCHAR(MAX) and could cause truncation.

A table with a Totals column is a perfect use case for computed columns in MSSQL. Making the Totals column a computed column allows MSSQL to do all the preassigned math for me, eliminating the need to write SQL or ColdFusion code to sum up the monthly totals. Every time I update the table I would have to run other code or SQL to again obtain the correct total.

Here's my table, MonthlyPay.

By turning Totals into a computed column, my life is easier. Here's how:

I could add even more calculations including the math to get tax percentages and including those in the total. So much easier!

We can combine SQL functions and use "LIKE" to filter data searching a string in a column. We are not using RegEx, just simple pattern matching. Here's our practical example.

We are given a table called "CityYear" with a column, "Title" that has this pattern "[city name] ([year])", e.g.;

Let's say we just want to pull the rows containing data for the current year only (2018 at the time of this post).

The first attempt could look like this:

However this returns no results. This is because we are matching to a string that literally contains "+ YEAR(GETDATE() ) +". Trying the following:

Gives us an error: Conversion failed when converting the varchar value '%' to data type int.

The above query returns exactly what we want:

The following query narrows down possible variations even more by conforming to the pattern and adding parenthesis to the comparison parameter.

If we want to find the previous year (in this case, 2017), we subtract from the YEAR(GETDATE()) function.

How does one select a distinct column value and still bring back all other associated columns for the distinct value?

In this example we have repeated [Requisition_Number]s and wish to find distinct values, but we still want the other columns returned also.

This ignores the DISTINCT and returns all the rows:

This returns JUST the distinct values for [Requisition_Number] and columns associated with those values.