I have this function to clear off the unwanted characters:
USE MASTER
GO
CREATE FUNCTION [dbo].[fn_StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
WITH ENCRYPTION, SCHEMABINDING
AS
BEGIN
SET @MatchExpression = '%['+@MatchExpression+']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
RETURN @String
END
I use that function in the following script that I working on to get the last time the machine has been rebooted:
I also Replace duplicate spaces with a single space in T-SQL, I don't want repeating spaces in the returned string.
--=================================
-- script to get last time the machine rebooted
-- and what sql server and host OS do we have?
-- Marcello Miorelli 16-mar-2020
--=================================
IF OBJECT_ID('TEMPDB.DBO.#RADHE') IS NOT NULL
DROP TABLE #RADHE
--create the table
CREATE TABLE #Radhe (logdate datetime not null default (getdate()),
processinfo varchar(108) not null default ('Radhe'),
the_text varchar(4000))
-- create a non-unique clustered index
CREATE CLUSTERED INDEX IXC_RADHE_RADHE ON #RADHE(logdate desc, processinfo asc)
-- load the table
INSERT #Radhe
exec sp_readerrorlog 0,1,'Copyright (c)'
SELECT @@SERVERNAME as [Server Name]
,R.logdate AS [Last Time Server Rebooted]
,REPLACE(
REPLACE(
REPLACE(
REPLACE(
master.dbo.fn_StripCharacters(R.the_text,
'^a-z0-9()#@.<>^:^-^ '),
'Copyright (C) 2019 Microsoft Corporation','')
,' ','<>')
,'><','')
,'<>',' ')
FROM #RADHE R
this on my current machine gives me:
and that is fine. However, when I go to check some other machine built in 2017 instead of 2019 then I get the following:
Question:
I want to get rid of the following piece of script, independent of the year (2019):
Copyright (C) 2019 Microsoft Corporation
What mask characters can I use on this situation?
Maybe I can achieve that with patindex? I have tried:
How to strip all non-alphabetic characters from string in SQL Server?
You can use PATINDEX
to find where in the string the pattern Copyright (C) [0-9][0-9][0-9][0-9] Microsoft Corporation
starts and then STUFF
to remove the 40 characters after that start point.
DECLARE @text VARCHAR(MAX) = 'Microsoft SQL Server 2017 (RTM-CU17) (KB4515579) - 14.0.3238.1 (X64) Sep 13 2019 15:49:57 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 18362: )'
SELECT STUFF(@text,PATINDEX('%Copyright (C) [0-9][0-9][0-9][0-9] Microsoft Corporation%',@text),40,'')