SysBlog

ColdFusion/MSSQL – new line and LIKE

SysBlog — Tue, 24 Mar 2026 11:52:41 +0000


SELECT id, ownership
					FROM tbl_test
					WHERE 

				lang = 
				and 

				(
					REPLACE(REPLACE(ownership, CHAR(13), ''), CHAR(10), '') like 
					OR
					REPLACE(REPLACE(ownership, CHAR(13), ''), CHAR(10), '') like 
					OR
					REPLACE(REPLACE(ownership, CHAR(13), ''), CHAR(10), '') like 
					OR
					REPLACE(REPLACE(ownership, CHAR(13), ''), CHAR(10), '') = 

					)

MSSQL – DATE and

SysBlog — Tue, 08 Apr 2025 08:03:32 +0000

SELECT TOP 5 LEFT(DATEOPEN, 10) FROM tbl_dates  where LEFT(DATEOPEN, 10) < '2021-01-01'

2019-10-16
2011-04-13
2014-06-13
2014-06-18
2012-03-29
2012-03-29

SELECT TOP 5 LEFT(DATEOPEN, 10) FROM tbl_dates where LEFT(DATEOPEN, 10) > '2021-01-01'

2022-05-20
2023-01-11
2022-05-05
2022-12-09
2021-06-21
2021-06-21

MSSQL: truncate all tables from schema with ‘test’ in the table name

SysBlog — Fri, 21 Mar 2025 13:04:52 +0000


DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + 'TRUNCATE TABLE  [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ' + CHAR(10)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'MYSCHEMA'  -- Change this to your schema
AND TABLE_NAME LIKE '%test%'  -- Find tables containing 'test' in the name

PRINT @sql  -- Check the generated SQL (optional)
EXEC sp_executesql @sql  -- Execute the truncation

MSSQL – variables

SysBlog — Thu, 16 Jan 2025 12:08:00 +0000

DECLARE @query_date DATE = '2025-01-15'
SELECT * from tbl_dates WHERE mydate = @query_date

Coldfusion – remove duplicates from array using struct

SysBlog — Thu, 28 Nov 2024 10:18:50 +0000

Coldfusion – inline if else

SysBlog — Wed, 05 Jun 2024 12:22:05 +0000

ColdFusion – send email using cfscript

SysBlog — Sat, 01 Jun 2024 07:46:09 +0000


// Create an instance of the mail object
mail=new mail();

// Set it's properties
mail.setSubject( "Sample Email" );
mail.setTo( "to@example.com" );
mail.setFrom( "from@example.com" );
mail.setCC( "cc@example.com" );
mail.setBCC( "bcc@example.com" );

// Add an attachment
mail.addParam( file="C:\foo.txt" );

// Add email body content in text and HTML formats
mail.addPart( type="text", charset="utf-8", wraptext="72", body="This is a test message." );
mail.addPart( type="html", charset="utf-8", body="This is a test message." );

// Send the email
mail.send();

source: https://stackoverflow.com/questions/8423276/how-do-you-send-an-email-with-an-attachment-using-cfscript-in-cf9

MSSQL – REMOVE non-alphabetic characters from string

SysBlog — Wed, 27 Mar 2024 07:35:38 +0000


Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')

source: https://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server

ColdFusion – OutOfMemoryError – GC overhead limit exceeded

SysBlog — Sun, 28 Jan 2024 10:03:52 +0000

“This error essentially means that you need to add more memory, or reconfigure your garbage collection arguments. You can suppress this error by adding -XX:-UseGCOverheadLimit to your JVM startup arguments”

Note to self – don’t do it! It will block your entire windows virtual machine! Already seen and done!!!

source:

https://www.petefreitag.com/blog/outofmemoryerror-gc-overhead/

ColdFusion – csv file BOM Header problem CF2023

SysBlog — Thu, 04 Jan 2024 15:15:50 +0000

I’m running cf2023 on windows and I’m having a weird problem with basic functionality that was working normally on the cf2016 version I was using earlier.
Every attempt to generate a csv file in which the Croatian characters will be displayed correctly has so far been unsuccessful.

I tried changing the JVM arguments and adding -Dfile.encoding=UTF8 but it didn’t help.

BOM header problem – adding #CHR(65279)# to the beginning of the csv content helped.

source: https://community.adobe.com/t5/coldfusion-discussions/adding-utf-8-bom-byte-order-mark-to-file-output/m-p/11745053