sql-serverstringt-sqlreplacepatindex

how to filter out the year using mask characters or any other trick in the string below?


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:

enter image description here

and that is fine. However, when I go to check some other machine built in 2017 instead of 2019 then I get the following:

enter image description here

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?


Solution

  • 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,'')