MSSQL Change Text within Column

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?

view plain print about
1SELECT [Question]
2 ,[Answer]
3 FROM [FAQ]
4 WHERE [Answer] LIKE '%2018%'

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

view plain print about
1UPDATE [FAQ]
2 SET [Answer] = REPLACE([Answer], '2018', '2019')
3 WHERE [Answer] LIKE '%2018%'

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.

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

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

ColdFusion CLIENT cookie issue

MSSQL error: The data types text and varchar are incompatible in the equal to operator.

In the ColdFusion administrator under there is the option to save CLIENT variables as cookies, in the registry, a datasource or not at all.

If the client variables are stored as cookies, it is fairly easy to retrieve and parse to determine things like the identity of a returning user from the cookie. This cookie is called CFCLIENT_<cfapplication name>. If the client variables are stored in the database, it's not so easy. Actually impossible, the cookie variables are NOT stored in the datasource.

First to locate the CLIENT variables, which are stored in a table called CDATA. CFGLOBALS client variables are session variables, like CFID, CFTOKEN and timestamp details and are stored in the table CGLOBAL.

So what about the error? The data types text and varchar are incompatible in the equal to operator. I ran some queries to try and see if there was some information I could glean from the client variables table.

view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] != ''
Error: The data types text and varchar are incompatible in the not equal to operator.

[data] is a text field permitting nulls. So I checked for null:

view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] is null
pulled up everything, to test, I ran this:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] is not null
which turned up nothing! So I added a space in the quotes:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] != ' '
The data types text and varchar are incompatible in the not equal to operator.
No go! But this worked:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] not like ''
which pulled up what I wanted to look at, but unfortunately could not use. No cookie info is stored in with the datasource client session storage option in the ColdFusion administrator.

MSSQL Remove duplicate records

MSSQL Remove duplicate records from a table leaving a single unique record.

The situation arose where we had multiple records for items where there should be only a single record. We have JobIDs which will have multiple unique TaskIDs. We found we several (lots) of JobIDs which had 2 of the same TaskIDs. It's OK to have many TaskIDs in each job, there just can't be 2 of the same TaskIDs per JobID. Many TaskIDs to one JobID and one unique TaskID to one JobID.

First we find which TaskIDs are duplicated.

view plain print about
1Select DISTINCT TaskID FROM (
2select JobID, TaskID, (*) as Count from JobTable
3Where TaskID IS NOT NULL
4Group by JobID, TaskID HAVING COUNT(*) >
1) X

We are going to remove the 2nd entry for each duplicate TaskID. The original TaskID is correct in this case. The 2nd TaskID is superfluous and will have a greater Identity Key, they were added at a later time. Let's pull up the duplicate TaskIDs with the larger Identity Key (ID).

view plain print about
1SELECT T1.ID
2FROM JobTable T1
3JOIN
4(SELECT JobID,TaskID , MAX(ID) AS ID FROM JobTable
5GROUP BY JobID,TaskID
6Having COUNT(*) >
1) AS T2
7ON T1.TaskID = T2.TaskID AND T1.Qbid = T2.QBID AND T1.ID = T2.ID

This pulls up what we want, so lets get rid of them.

view plain print about
1DELETE JobTable WHERE ID in (
2SELECT T1.ID
3FROM JobTable T1
4JOIN
5(SELECT JobID,TaskID , MAX(ID) AS ID FROM JobTable
6GROUP BY JobID,TaskID
7Having COUNT(*) >
1) AS T2
8ON T1.TaskID = T2.TaskID AND T1.Qbid = T2.QBID AND T1.ID = T2.ID
9)

Done! Now we have unique TaskIDs for each JobID.

Find Duplicates in MSSQL

Find duplicate entries in MS SQL table columns. Need to see if you have 2 or more duplicate entries in your table? Want to find out if the key is really unique and there are not multiples? Simple!

view plain print about
1SELECT [ID], COUNT([TaskID])as NumberOfDups
2FROM [dbo].[MyBigTable]
3GROUP BY ID,[TaskID]
4HAVING (COUNT([TaskID]) >
1 )

ID = the identifier, could be a name or whatever
TaskID = looking for duplicates of this

Trouble with cfloop lists and arrays with cfqueryparam

I was looping an array and and using each item in a cfqueryparam. Running a query in each loop. Each iteration was displaying a null query result, which was incorrect. The same problem occurred with a list of the same items.

The simple answer was to surround the cfqueryparam value with the Trim() function! It worked with the list loop too.

view plain print about
1<cfqueryparam value="#Trim(MSArray[j])#" cfsqltype="CF_SQL_VARCHAR">

Here is my effort:

view plain print about
1<cfset MSList = "
2010,
3020,
4004,
5075.5,
6027,
7030,
8020.5,
9021,
10023,
11041,
12041.1,
13041.5,
14041.3,
15040,
16080,
17102,
18088.1,
19095,
20104.5,
21104.1,
22104.3,
23110,
24096,
25100,
26107.1,
27112,
28124,
29025,
30101,
31143,
32143.1,
33126,
34132,
35146,
36149,
37153,
38154,
39155,
40160
41"

42/
>

43
44<cfloop list="#MSList#" index="j">
45    <cfquery datasource="#myDataSource#" name="getID">
46         SELECT [ID]
47         FROM     [dbo].[MS_Table]
48         where [Activity_ID] = 1
49         and     [MS] = <cfqueryparam value="#Trim(j)#" cfsqltype="CF_SQL_VARCHAR">
50    </cfquery>
51    <cfdump var="#getID#">
52</cfloop>

More Entries

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