≡ Menu

Comparing Multiple Patterns with LIKE Operator

pic of Facebook Like button

I really like the LIKE operator! (See what I did there?)

I use it on a daily basis. It does have one frustrating shortcoming, however. Unlike the IN operator (where a list is used for comparison), you can only compare one pattern with each LIKE statement.

If you have only a few patterns, this isn’t too much of a problem.

SELECT * FROM tbl_ThatTableOverThere
WHERE ThisColumn LIKE '%pattern1%'
OR ThisColumn LIKE '%pattern2%'
OR ThisColumn LIKE '%pattern3%'
-- [...]
OR ThisColumn LIKE '%patternX%';

But what if X is a very large number? Hundreds? Thousands? Maybe even millions?

I wrote the code below to allow for multiple patterns to be compared. The patterns are inserted into a table variable (@patterns) and are selected using a fast forward cursor. Unless the number of patterns is truly astronomical, this should suffice.

USE DatabaseName
GO
SET NOCOUNT ON
DECLARE @random INT, @upper INT, @lower INT, @randomTable NVARCHAR(20)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @whereClause NVARCHAR(MAX)
DECLARE @sourceTable NVARCHAR(255)
DECLARE @columnName NVARCHAR(255)
DECLARE @pattern NVARCHAR(255)
DECLARE @patterns TABLE (pattern NVARCHAR(255) NOT NULL)
DECLARE @deleteTempTable NVARCHAR(MAX)

/* These variables should remain unchanged unless the name of the 
temporary table needs to have different text or range of random numbers
for the suffix of the temp table. 
[Reference: https://stackoverflow.com/questions/14756780/creating-a-temporary-table-name-with-a-randomly-generated-number] */
SET @lower = 1
SET @upper = 9999999
SELECT @random = ROUND(((@upper - @lower) * RAND() + @lower),0)
SELECT @randomTable = '##search'+CAST(@random AS NVARCHAR(20))+''

-- Name of source table holding data for comparison
SET @sourceTable = 'tbl_Name'

-- Column that is being compared with patterns using LIKE operator
SET @columnName = 'ColumnName'

-- Additional WHERE clause may be specified here
SET @whereClause = '' -- e.g., 'AND AnyColumnName LIKE ''%b%''' 

-- Insert patterns into table variable below
INSERT INTO @patterns VALUES
('%pattern1%')
-- ,('%pattern2%')
-- ,(%pattern3%) and so on

-- Deletes temp table if it already exists
SET @deleteTempTable = 'IF object_id(''tempdb..' + @randomTable + ''') IS NOT NULL DROP TABLE ' + @randomTable
EXEC(@deleteTempTable)

-- This line creates the temporary table using the same schema as the source table without inserting data.
-- The UNION ALL operator prevents the IDENTITY property from being applied to any of the columns in the temp table.
-- [Reference: https://dba.stackexchange.com/questions/916/how-do-i-copy-a-table-with-select-into-but-ignore-the-identity-property]
EXEC('SELECT TOP 0 * INTO ' + @randomTable + ' FROM ' + @sourceTable + ' UNION ALL SELECT TOP 0 * FROM ' + @sourceTable)

SET @SQL = 'INSERT INTO ' + @randomTable + CHAR(13) + 'SELECT TOP 0 * FROM ' + @sourceTable

DECLARE patternsCursor CURSOR FAST_FORWARD FOR
SELECT pattern FROM @patterns

OPEN patternsCursor
FETCH NEXT FROM patternsCursor INTO @pattern

WHILE @@FETCH_STATUS = 0
BEGIN
	IF LEN(@pattern) > 0
	BEGIN
		--Note: UNION operator (as opposed to UNION ALL) is used here to prevent duplicate rows from being inserted into table variable
		--[Reference: https://www.techonthenet.com/sql/union_all.php]
		SET @SQL = @SQL + CHAR(13) + 'UNION' + CHAR(13) + 'SELECT * FROM ' + @sourceTable + CHAR(13) + 'WHERE ' + @columnName + ' LIKE ''' + @pattern + ''''
		IF LEN(@whereClause) > 0 SET @SQL = @SQL + CHAR(13) + @whereClause
	END

	FETCH NEXT FROM patternsCursor INTO @pattern
END
CLOSE patternsCursor
DEALLOCATE patternsCursor
SET @SQL = @SQL + ';'

-- Prints final SQL statement on Results tab
SELECT @SQL
SET NOCOUNT OFF
-- Executes SQL Statement showing number of rows inserted on Messages tab
EXEC(@SQL)
SET NOCOUNT ON

-- Executes SQL Statement showing rows in Results tab
EXEC('SELECT * FROM ' + @randomTable + ';')

-- Deletes temp table
EXEC(@deleteTempTable)
Oracle Primavera P6 EPPM

I support an environment where users access the Primavera P6 thick client on Windows via Citrix.

If I connect to the Database Configuration app and add, modify, or delete the connection to a P6 database, the PrmBootStrap.xml file that is in my profile. For the version we are using (8.2), that location is C:\Users(username)\AppData\Local\Oracle\Primavera P6\P6 Professional. It appears that the file is also copied to the “%PROGRAMDATA%\Oracle\Primavera P6\P6 Professional” folder and the “All Users” profile (if it exists) as well, in “C:\Users\All Users\AppData\Local\Oracle\Primavera P6\P6 Professional”.
In the code below, I copy from the ProgramData folder instead of from my profile, but it would be easy to eliminate the outer for loop and uncomment two lines to make it copy from my profile.

Since I have multiple Citrix servers for P6, I normally would manually copy the file to the folders on the other Citrix servers so that the users will have the same list no matter where they log on. To simplify this, I wrote this Windows batch script to automate this copy process.

@echo off
cls
setlocal EnableDelayedExpansion
rem //Citrix servers hosting Primavera P6 should be in the serverlist variable, separated by spaces
set serverlist=citrixserver1 citrixserver2 citrixserver3
rem //Windows username of person who has standard file
set usersname=foobar

set programdataunc=%PROGRAMDATA::=$%
set filepath=Oracle\Primavera P6\P6 Professional
set filename=PrmBootStrap.xml

rem set Sourceloc="C:\Users\%usersname%\AppData\Local\%filepath%\%filename%"

for %%s in (%serverlist%) do (	
	set Sourceloc="\\%%s\%programdataunc%\%filepath%\%filename%"
	
	if not exist !Sourceloc! (
		echo No file found: !Sourceloc!
	) else (
		for %%a in (!Sourceloc!) do set SourceFileDate=%%~ta

		set offset=0
		if "!SourceFileDate:~-2!" == "PM" set offset=12					
		set /a "SourceFileDateHour=!SourceFileDate:~11,2!+!offset!"
		set "SourceFileDateCompare=!SourceFileDate:~6,4!!SourceFileDate:~0,2!!SourceFileDate:~3,2!!SourceFileDateHour!!SourceFileDate:~14,2!"					

		for %%x in (%serverlist%) do (
			echo.			
			echo DestinationServer = %%x
			echo SourceServer = %%s
			echo .
			if not %%x==%%s (
				set firstloc="\\%%x\%programdataunc%\%filepath%\%filename%"
				set secondloc="\\%%x\C$\Users\All Users\%filepath%\%filename%"
				
				for %%y in (!firstloc! !secondloc!) do (			    
					if exist %%y (						
						echo =-=-=-=-=-=
						for %%a in (%%y) do set DestinationFileDate=%%~ta
						set offset=0
						if "!DestinationFileDate:~-2!" == "PM" set offset=12					
						set /a "DestinationFileDateHour=!DestinationFileDate:~11,2!+!offset!"
						set "DestinationFileDateCompare=!DestinationFileDate:~6,4!!DestinationFileDate:~0,2!!DestinationFileDate:~3,2!!DestinationFileDateHour!!DestinationFileDate:~14,2!"							
						echo DestinationFileDateCompare = !DestinationFileDateCompare!
						echo SourceFileDateCompare = !SourceFileDateCompare!
						echo .
						if "!SourceFileDateCompare!" gtr "!DestinationFileDateCompare!" (					    
							set NewDestinationFileName=!filename!_!DestinationFileDateCompare!
							echo !NewDestinationFileName!
							ren %%y !NewDestinationFileName!
							copy !Sourceloc! %%y						
						) else (							
							echo Destination file %%y is newer than or the same as !Sourceloc!.
						)
						echo =-=-=-=-=-=
						echo.
					) else (
						echo.
						echo =x=x=x=x=x=
						echo INFO: %%y not found					
						echo =x=x=x=x=x=
						echo.
					)  
				)
			) else (
				echo Destination and Source servers are the same: no file copied.
			)
		)
	)
)
rem pause

If you uncomment the pause command at the end, you can look at the command window to make sure it’s working properly.

Stripping HTML Tags from Textboxes Using JavaScript

HTML tags

One of the applications I support routinely has a problem where users cut and paste text that contains HTML tags from other sources into the text boxes on entry forms in my application. Most of the time, this does not cause a problem. However, if the users do not capture all of the text, they may unwittingly not grab the closing tags. When that happens, all sorts of fun things can happen to the main page on the app, which shows recent entries.

I decided I’d like to remove all HTML, except I’d like to retain line breaks. Based partially on a solution I found on Stack Overflow, I made this function.

this.fixHtml =
	function(strHtml)
	{
		var div = document.createElement('div');
		strHtml = strHtml.replace(/<br>/gi,'\n');
		strHtml = strHtml.replace(/<br \/>/gi,'\n');
		div.innerHTML = strHtml;
		strHtml = div.innerText || div.textContext;
		strHtml = strHtml.replace(/\n/gi,'<br />');
		div.innerHTML = strHtml;			
		return div.innerHTML;
	};

I call the fixHtml function whenever an input tag is of type text, or if a div element is of the class “mimicTextArea” (it looks like a text box):

if(oForm!=null)
{
	var oInputs = oForm.getElementsByTagName("input");
	for(var x=0;x<oInputs.length;x++)
	{
		if(oInputs[x].type=="text" && oInputs[x].value.length > 0)
		{
			oInputs[x].value = self.fixHtml(oInputs[x].value);
		}
	}
	
	var oDivs = oForm.getElementsByTagName("div");
	for(var x=0;x<oDivs.length;x++)
	{
		if(oDivs[x].className == "mimicTextArea" && oDivs[x].innerHTML.length > 0)
		{
			oDivs[x].innerHTML = self.fixHtml(oDivs[x].innerHTML);
		}
	}
	
}

This seems to serve my purpose well. Should I wish to retain other specific HTML tags, a similar technique may be used as above if they are self-closing tags such as the line break tag. Tags that require an opening and a closing tag would require further development.

Problems with Installing Applications on Windows Servers via Remote Desktop

Terminal Server remote client not allowed dialog box

If you have tried to install an application on a Windows Server when logged in with Remote Desktop Connection and the result was a dialog box like the one above, this post is for you.

Understandably, many software companies want different kinds of licensing to be used when installing on a server running Terminal Services. The assumption is that you will likely be using it as a Citrix server, allowing many users to access the application at once. If this is what you are intending to do, then you must follow the licensing scheme required by the software vendor.

However, what if your intention is to install it only for yourself? In my case, I have a virtual development server that no one else uses, but I log onto it with Remote Desktop. When trying to install an application the other day, I got a message similar to the one above. How does it know that I’m logging on remotely?

Other users have had similar experiences, and some of the suggestions on this post on SuperUser gave me an idea.

What if I was logged onto the Console rather than an RDP session? This is a virtual server that is not physically located in the same place as me, so that seemed unlikely. Then I remembered VNC! After installing a free copy of UltraVNC Server on the server and Viewer on my PC, I was able to log in as an apparent session from the Console.

The application was satisfied and allowed me to install it without any further complications.