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.;

view plain print about
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:

view plain print about
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:

view plain print about
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%' + YEAR(GETDATE ()) + '%'

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

view plain print about
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%' + CAST(YEAR(GETDATE()) as varchar(4)) + '%'

The above query returns exactly what we want:

view plain print about
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.

view plain print about
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.

view plain print about
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%(' + CAST(YEAR(GETDATE()) -1 as varchar(4)) + ')%'

view plain print about
1Title
2Los Angeles (2017)
3Denver (2017)
4Dallas (2017)

Breaking Up a String into Equal Parts

Presented with a string of concatenated ID's, I had to break them up into a comma delimited list. These ID's were all stored in a database field concatenated in one long string with no breaks, tabs or other delimiters. Why, I don't know.

Fortunately, I knew that all the ID's were 6 characters each. I just had to come up with the regular expression to separate them and insert a comma and a space.

Here's the ColdFusion code to do that:

view plain print about
1<cfset MyLongStringOfConcatenatedIDs = "1234567890qwertyuiopasdfghjklz" />
2
3<cfset CommaDelimList =
4REReplace(Trim(MyLongStringOfConcatenatedIDs), "(.{6})\B", "\1, ", "ALL") /
>

5
6output: "123456, 7890qw, ertyui, opasdf, ghjklz"

Integers Only, Remove Dollar Signs and Periods

Looking to remove "$" and "." (dollar signs, commas and periods/decimal points) from numbers before inserting into a database, storing integers. This means decimals need to be rounded into an integer.

ColdFusion only... We have to use ColdFusion to Round the decimals.

view plain print about
1<cfset cleanNumber = Round(Replace(Replace(DirtyNumber, "$", ""), ",", "", "ALL")) />
The first inside, Replace removes the $. The second or outer Replace removes all commas, while the Round function converts the decimal to an integer.

Here is the use of regex, regular expressions and ColdFusion:

view plain print about
1<cfset cleanNumber = Round(REReplace(DirtyNumber, "[^0-9.]", "", "ALL")) />
With this filter, the regex removes all non-numeric characters except the decimal points. The Round function does it's job rounding the closest integer.

I wanted to use only regex but regex cannot do math, i.e., round the decimal. The first statement only removed "$" and "." and ",". With JavaScript filtering on the front end the first example addresses 99% of the cases but the second is more thorough and removes all occurrences of non-numeric characters except decimal points. The only downfall to both is if there is more than one period / decimal point.

Trim characters off of right on a string

Trim characters off of right on a string. Trim off a certain number of characters on the right side of a string. Here's a quick easy method to cut off an exact number off the right portion without reversing the string and using various other string manipulation techniques. The concept is simple, find the length of the string

view plain print about
1len(MyString)
and then subtract the number of characters you want removed, e.g., 3
view plain print about
1len(MyString) - 3
We just use this result to count thenumber of characters from the left and keep those using left() function.
view plain print about
1left(MyString, (len(MyString) - 3))

RegEx for Numbers and Dollar Signs and Periods or Commas

Need some regular expressions for numbers with dollar signs and commas or periods?

I came across a scenario where we needed to filter field values which could possibly include text and all other string characters and just perform action on some dirty numbers. By dirty numbers I mean data for money and square footage, which would include dollar signs ($), periods (.) and commas (,).

The data needed to be filtered to determine if did just contain numbers, AND commas, dollar signs or periods. Any fields with text or other characters were to be ignored.

Here is the regular expression, reg ex, used:

view plain print about
1^[,0-9\.\$]+$

The CFML REFind check looks like this:

view plain print about
1REFind("^[,0-9\.\$]+$", fieldValue)
with "fieldValue" being the variable.

We just wanted to remove the commas from all numeric values and change commas in text values to semi colons. The whole regex check and action looks like this:

view plain print about
1<cfif REFind("^[,0-9\.\$]+$", fieldValue)>
2 <cfset fieldValue = Replace(fieldValue,",","","ALL")>
3 <cfelse>
4 <cfset fieldValue = Replace(fieldValue,",",";","ALL")>
5</cfif>

Just a few minor notes... The dollar sign ($) and period (.) are special characters and have to be escaped by preceding with a forward slash (\).

Here are some example tests used:

view plain print about
112344,234,345 = #ReFind("^[,0-9\.\$]+$","12344,234,345")#<br/>
2$12344,234,345 = #ReFind("[,0-9\.\$]+$","$12344,234,345")#<br/>
3$12344,234.345 = #ReFind("[,0-9\.\$]+$","$12344,234.345")#<br/>
412344.234.345 = #ReFind("^[,0-9\.\$]+$","12344.234.345")#<br/>
5as$dfa2,32344 = #ReFind("^[,0-9\.\$]+$","as$dfa2,32344")#<br/>
6asdfasdf,rt$yu = #ReFind("^[,0-9\.\$]+$","asdfasdf,rt$yu")#<br/>
7$123412.4567 = #ReFind("^[,0-9\.\$]+$","$123412.4567")#<br/>
8asdfa,12341.3456.sdfg,dgfh = #ReFind("^[,0-9\.\$]+$","asdfa,12341.3456.sdfg,dgfh")#<br/>
9It cost me $33.00 = #ReFind("^[,0-9\.\$]+$","It cost me $33.00")#<br/>
10It cost me $33 = #ReFind("^[,0-9\.\$]+$","It cost me $33")#<br/>
11It cost me 33.00 = #ReFind("^[,0-9\.\$]+$","It cost me 33.00")#<br/>

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