SysBlog » MSSQL Database Development

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

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

MSSQL: Get size in KB of all database tables

SysBlog — Mon, 23 Oct 2023 10:31:36 +0000

SELECT
t.name AS TableName,
s.name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.object_id = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.name NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.object_id > 255
GROUP BY
t.name, s.name, p.rows
ORDER BY
TotalSpaceMB DESC, t.name

Source: https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database

MSSQL: select only row with max value

SysBlog — Thu, 04 May 2023 10:49:16 +0000

Another approach for http://blog.interakt.hr/?p=610

select
	id, case_id, file_path, date_created
  from (
        select id, case_id, file_path, date_created, row_number() over (partition by case_id order by date_created desc) as rn
         from TMP_reminders
        ) as dtbl_unique_reminders
  where dtbl_unique_reminders.rn = 1

SOURCE: https://stackoverflow.com/questions/38912808/select-only-row-with-maxid-in-sql-server

MSSQL – get file extension from file name

SysBlog — Sat, 25 Feb 2023 12:21:53 +0000

 select (case when FileName like '%.%' then reverse(left(reverse(FileName), charindex('.', reverse(FileName)) - 1)) else '' end) as File_Extension

source: https://stackoverflow.com/questions/16783026/t-sql-get-file-extension-name-from-a-column

MSSQL: find string in string

SysBlog — Wed, 07 Dec 2022 12:08:38 +0000


SELECT * FROM tbl_companies WHERE CHARINDEX('test', Company_name) > 0

MSSQL – calculate season

SysBlog — Sun, 13 Nov 2022 11:38:01 +0000


SELECT
(case
		when month(GETDATE()) = 12  then case when day(GETDATE()) < 21 then 'autumn' else 'winter' end
		when month(GETDATE()) in ( 1,2 ) then 'winter'
		when month(GETDATE()) = 3 then case when day(GETDATE()) < 21 then 'winter' else 'spring' end
		when month(GETDATE()) in ( 4, 5 ) then 'spring'
		when month(GETDATE()) = 6  then case when day(GETDATE()) < 21 then 'spring' else 'summer' end
		when month(GETDATE()) in ( 7, 8 ) then 'summer'
		when month(GETDATE()) = 9  then case when day(GETDATE()) < 23 then 'summer' else 'autumn' end
		when month(GETDATE()) in ( 10, 11 ) then 'autumn'
end) as season