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.;
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
Title
Los Angeles (2015)
Los Angeles (2016)
Los Angeles (2017)
Los Angeles (2018)
Denver (2015)
Denver (2016)
Denver (2017)
Denver (2018)
Dallas (2015)
Dallas (2016)
Dallas (2017)
Dallas (2018)
1Title
2Los Angeles (2015)
3Los Angeles (2016)
4Los Angeles (2017)
5Los Angeles (2018)
6Denver (2015)
7Denver (2016)
8Denver (2017)
9Denver (2018)
10Dallas (2015)
11Dallas (2016)
12Dallas (2017)
13Dallas (2018)
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:
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
SELECT Title
FROM CityYear
WHERE Title LIKE '% + YEAR(GETDATE() )+ %'
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '% + YEAR(GETDATE() )+ %'
However this returns no results. This is because we are matching to a string that literally contains "+ YEAR(GETDATE() ) +". Trying the following:
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
SELECT Title
FROM CityYear
WHERE Title LIKE '%' + YEAR(GETDATE ()) + '%'
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%' + YEAR(GETDATE ()) + '%'
Gives us an error: Conversion failed when converting the varchar value '%' to data type int.
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
SELECT Title
FROM CityYear
WHERE Title LIKE '%' + CAST(YEAR(GETDATE()) as varchar(4)) + '%'
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%' + CAST(YEAR(GETDATE()) as varchar(4)) + '%'
The above query returns exactly what we want:
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
Title
Los Angeles (2018)
Denver (2018)
Dallas (2018)
1Title
2Los Angeles (2018)
3Denver (2018)
4Dallas (2018)
The following query narrows down possible variations even more by conforming to the pattern and adding parenthesis to the comparison parameter.
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
SELECT Title
FROM CityYear
WHERE Title LIKE '%(' + CAST(YEAR(GETDATE()) as varchar(4)) + ')%'
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%(' + CAST(YEAR(GETDATE()) as varchar(4)) + ')%'
If we want to find the previous year (in this case, 2017), we subtract from the YEAR(GETDATE()) function.
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
SELECT Title
FROM CityYear
WHERE Title LIKE '%(' + CAST(YEAR(GETDATE()) -1 as varchar(4)) + ')%'
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%(' + CAST(YEAR(GETDATE()) -1 as varchar(4)) + ')%'
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
Title
Los Angeles (2017)
Denver (2017)
Dallas (2017)
1Title
2Los Angeles (2017)
3Denver (2017)
4Dallas (2017)