Combining MSSQL Functions and Wildcards to Filter Data
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.;
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:
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:
2FROM CityYear
3WHERE Title LIKE '%' + YEAR(GETDATE ()) + '%'
Gives us an error: Conversion failed when converting the varchar value '%' to data type int.
2FROM CityYear
3WHERE Title LIKE '%' + CAST(YEAR(GETDATE()) as varchar(4)) + '%'
The above query returns exactly what we want:
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.
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.
2FROM CityYear
3WHERE Title LIKE '%(' + CAST(YEAR(GETDATE()) -1 as varchar(4)) + ')%'
2Los Angeles (2017)
3Denver (2017)
4Dallas (2017)
There are no comments for this entry.
[Add Comment] [Subscribe to Comments]