Computed Columns MS SQL

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.

view plain print about
1SELECT [ID]
2 ,[Year]
3 ,[January]
4 ,[February]
5 ,[March]
6 ,[April]
7 ,[May]
8 ,[June]
9 ,[July]
10 ,[August]
11 ,[September]
12 ,[October]
13 ,[November]
14 ,[December]
15 ,[Totals]
16 FROM [MonthlyPay]

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

view plain print about
1ALTER TABLE [MonthlyPay] DROP COLUMN [Totals];
2GO
3ALTER TABLE [MonthlyPay] ADD [Totals] AS (
4[January] + [February] + [March] + [April] + [May] + [June] + [July] + [August] + [September] + [October] + [November] + [December]) PERSISTED;

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

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)

Select Distinct and Return Multiple Columns

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:

view plain print about
1SELECT [ID]
2        ,DISTINCT [Requisition_Number]
3        ,[Status]
4        ,Title]
5        ,[Code]
6        ,[UID]
7        ,[Full_Name]
8        ,[City]
9        ,[State]
10        ,[Requisition_Justification]
11        ,[Date_Requisition_Opened]
12    FROM [Requisitions]
and is NOT what we want.

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

view plain print about
1SELECT     [ID]
2        ,[Requisition_Number]
3        ,[Status]
4        ,Title]
5        ,[Code]
6        ,[UID]
7        ,[Full_Name]
8        ,[City]
9        ,[State]
10        ,[Requisition_Justification]
11        ,[Date_Requisition_Opened]
12    FROM (
13SELECT     [ID]
14        ,[Requisition_Number]
15        ,[Status]
16        ,Title]
17        ,[Code]
18        ,[UID]
19        ,[Full_Name]
20        ,[City]
21        ,[State]
22        ,[Requisition_Justification]
23        ,[Date_Requisition_Opened]
24        ,ROW_NUMBER() OVER(PARTITION BY [Requisition_Number] ORDER BY [ID]) RN
25    FROM [Requisitions]) R
26    WHERE RN = 1

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.

not equal to null not working

In MSSQL, looking for rows that are not equal to a certain value does not return rows with null values. To retrieve all values that are not equal and are null, you have to add both clauses "not equal" and "is null" to return all values not equal.

In SQL checking for NOT EQUAL ("!=" or "<>") will not return NULL values. NULL has no value and cannot be compared using scalar value operators.

I had to change my WHERE clause from:

view plain print about
1[whatever] != 1
To
view plain print about
1([whatever] != 1 OR [whatever] is null)

CTE with Recursion Example

Common Table Expressions with Recursion using WITH. A Common Table Expression is a temporary result set upon which we can work against. It's kind of like a sub query in memory that we can manipulate. Referencing this temporary table to create a subset is an example of recursion. This example may help explain and provide reference to create your own CTE with Recursion query.

This is very easy in ColdFusion but not so in Microsoft SQL Server or TSQL. We'd like to keep it on the database side because it is so much faster with less overhead on servers and network. It is a lot faster than trying to use sub-queries too.

We are given this table called dbo.JoeTest1:

view plain print about
1EVID    BillDocket
2191    External:277
3192    External:277
4193    External:277
5194    External:277
6195    Internal:117
7197    Internal:HB2255
8206    External:4;5;7
9206    Action:123
10206    Combo:1;0;67
11206    Action:1321;5445;3545
12206    Internal:1233;1323
13207    Internal:11;22;33
14207    External:44;55;66
15211    External:729
16214    External:481

Notice EVIDs 206 and 207. They have multiple records. We want to combine those records by concatenating the BillDocket and showing one result set for each unique EVID.

We want the results to look like the following (again, notice 206 & 207):

view plain print about
1EVID    BillDocket
2191    External:277
3192    External:277
4193    External:277
5194    External:277
6195    Internal:117
7197    Internal:HB2255
8206    Action:123,Action:1321;5445;3545,Combo:1;0;67,External:4;5;7,Internal:1233;1323
9207    External:44;55;66,Internal:11;22;33
10211    External:729
11214    External:481

Compare that to the table above, looking at 206 & 207. We also have 5 less rows in the result set due to the combining of column values.

How do we get this result? Here's the query with some explanations commented inline:

view plain print about
1-- we use the 'WITH' CTE to create the 'anchor' table
2-- also define the temporary table name and columns
3WITH Bills (EVID, BillDocket)
4AS
5(
6-- this is the anchor table upon which we will recurse
7-- if run separately, this query will return the 1st table above
8SELECT J.EVID, BillDocket
9FROM [dbo].JoeTest1 J
10)
11(
12-- this does the recursion
13SELECT Bills2.EVID,
14    -- stuff command, concatenates and combines for us
15    STUFF(
16        -- we are adding the comma plus the next BillDocket to the string
17        (SELECT DISTINCT ',' + J1.BillDocket
18            FROM [dbo].JoeTest1 J1
19            -- matched the anchor to the CTE table
20            WHERE Bills2.[EVID] = J1.[EVID]
21                FOR XML PATH (''))
22            , 1, 1, '')
23        AS BillDocket
24FROM Bills Bills2
25GROUP BY Bills2.EVID
26)
27-- have to keep this to break any type of infinite loop we may cause
28OPTION (MAXRECURSION 1000);

convert UTC date in Unix timestamp format

Converting a date in ColdFusion into a UNIX timestamp format is not straight forward if you don't know what the UNIX timestamp format is. Unix time is defined as the number of seconds that have elapsed since midnight Coordinated Universal Time (UTC), 1 January 1970. Here's how you convert using ColdFusion.

view plain print about
1DateDiff("s", CreateDate(1970,1,1), CreateODBCDateTime(Now()))

Google Custom Search Results

Want to find out what Google has on your web site? Do you wish to tabulate a list of all the pages Google has searched and indexed? Here's how to document it! Caveat: you must have a google business account to use the API and not have ads returned with your results.

To view what google has indexed, go to Google, of course, and type in your site's domain, prefixed with "site:", e.g., "site:mysite.com". This will give you the first 10 results. You could copy and paste, but you are too smart and lazy for that.

This tutorial will NOT go into how to set up Google Custom Search or access the API. I'm assuming you either already know how to do that or will look that up on your own. The Google Custom Search API returns a whole bunch of stuff, but we only want in this case, the URLs of every page indexed.

I have the CFM template in its entirety below, but we'll step through what's going on here first. The form which calls itself for the action.

view plain print about
1<p class="welcome">Googe Custom Search</p>
2<form action="joe-google-custom-search.cfm">
3    <label>
4        Search Terms:
5        <input type="text" name="googlesearchterms" id="googlesearchterms" size="30" />
6    </label>
7    <button id="googlesearch" name="googlesearch" type="submit">Search</button>
8</form>
To submit the form, you would want to enter, "site:yourdomain.com". Or you could put in whatever search terms you wish. Once the form is submitted, we want to format the search terms by converting the spaces to plus signs (+). The query string will be appended to the API call's URL.
view plain print about
1<cfset searchterms = Replace(trim(form.googlesearchterms, " ", "+", "all")) />

We want to set our counter and make sure we get a maximum of 1000 returns, if there are that many. This will error out at the end. We are going to go in increments of 10 because that's the default number of results Google will return.

view plain print about
1<!--- set our result counter --->
2    <cfset CountVar = 1 />
3    <!--- limit returns to 1000 --->
4    <cfloop condition="CountVar LESS THAN 1000">

This is where the action happens. We add our query string, our API key and our incremented count.

view plain print about
1<!--- hit up Google --->
2        <cfhttp url="https://www.googleapis.com/customsearch/v1?key=#myGoogleAPIKey#&q=#searchterms#&start=#CountVar#"
3                resolveURL="yes"
4                result="variables.response"
5                method="get">

We will have to convert the returned JSON to an easily usable format:
view plain print about
1<cfset variables.GoogleSearchResults = deserializeJSON(variables.response.Filecontent) />

Then pluck out our array of returned result items:

view plain print about
1<cfset variables.GoogleSearchResults = deserializeJSON(variables.response.Filecontent) />

The results come back in groups of 10, so we have to loop this set and write to our file, which should already be created and in place. You can output to the screen too, but that's a bit too much. For testing I set the CountVar to 100 and returned to the screen, a reasonable amount for verification.

view plain print about
1<!--- loop thru and add to list (dump to screen) --->
2        <cfloop array="#variables.GoogleArray#" index="arrayitem">
3            <cffile file="C:\path\to\my\text\file\googlesearchtext.txt" action="append" output="#arrayitem.link#" />
4            <!--- <cfoutput>#arrayitem.link#</cfoutput><br/> --->
5        </cfloop>

Check your text file, it should be much larger now. From here you can cut and paste into Excel or whatever format you need. If desired, you could create a SQL insert statement too.

Here's all the code in one piece, it's called joe-google-custom-search.cfm. So if you change the name, don't forget the form action attribute.

view plain print about
1<p class="welcome">Googe Custom Search</p>
2<form action="joe-google-custom-search.cfm">
3    <label>
4        Search Terms:
5        <input type="text" name="googlesearchterms" id="googlesearchterms" size="30" />
6    </label>
7    <button id="googlesearch" name="googlesearch" type="submit">Search</button>
8</form>
9
10
11<!--- form submitted? --->
12<cfif StructKeyExists(form, "googlesearchterms") />
13    
14    <!--- format search terms --->
15    <cfset searchterms = Replace(trim(form.googlesearchterms, " ", "+", "all")) />
16    <cfset myGoogleAPIKey = "123412342342341234:oiuo12341234" />
17    
18    <!--- set our result counter --->
19    <cfset CountVar = 1 />
20    <!--- limit returns to 1000 --->
21    <cfloop condition="CountVar LESS THAN 1000">
22        
23        <!--- hit up Google --->
24        <cfhttp url="https://www.googleapis.com/customsearch/v1?key=#myGoogleAPIKey#&q=#searchterms#&start=#CountVar#"
25                resolveURL="yes"
26                result="variables.response"
27                method="get">

28    
29        <!--- pick out what we want --->
30        <cfset variables.GoogleSearchResults = deserializeJSON(variables.response.Filecontent) />
31        <cfset variables.GoogleArray = variables.GoogleSearchResults.items />
32    
33        <!--- loop thru and add to list (dump to screen) --->
34        <cfloop array="#variables.GoogleArray#" index="arrayitem">
35            <cffile file="C:\path\to\my\text\file\googlesearchtext.txt" action="append" output="#arrayitem.link#" />
36            <!--- <cfoutput>#arrayitem.link#</cfoutput><br/> --->
37        </cfloop>
38    
39        <!--- Google returns results in groups of 10 --->
40        <cfset CountVar = CountVar + 10 />
41    </cfloop>
42</cfif>

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))

More Entries

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