T-SQL Tuesday #154 Invitation – SQL Server 2022

Posted on September 13, 2022September 13, 2022 by David

Introduction

This is my first time participating in T-SQL Tuesday. The Invitation from Glenn Berry asks to write about what you’ve been doing with SQL 2022.

Getting DBA Dash ready for SQL 2022

I was eager to test DBA Dash with SQL 2022 and started testing with the first public release (CTP 2.0). I added a SQL 2022 instance to my lab environment, created using AutomatedLab.

Unlike my other lab machines, I ran through the installer for 2022 manually to get up and running quickly and test the new installer.

DBA Dash only needed some minor modifications to work with SQL 2022. The first and most important step was to add version 16 to the hardcoded list of supported versions and also update some static data for SQL Server 2022. I hardcode the list of supported versions to give me the opportunity to test new versions first and fix any issues.

This initial support for SQL 2022 was added in May. I’ve been running DBA Dash in my lab environment since then and it works well.

DBA Dash – support for new features in SQL 2022

QAT_DEFLATE Backup Compression

Version 2.22.0 adds support for capturing the compression algorithm used on SQL 2022.

Glenn Berry has some good articles on this new backup compression algorithm here and here. I’m getting similar results to Glenn with my own testing using QAT_DEFLATE (software mode) – faster backups, higher compression and lower resource utilization.

sys.databases

SQL 2022 adds some new columns to sys.databases:

is_data_retention_on
is_ledger_on
is_change_feed_enabled

I’m planning to add these to the DBA Dash collection. Only is_ledger_on is currently documented.

Other

On the to-do list is to test DBA Dash with contained availability groups. DBA Dash has monitoring for agent jobs and availability groups so it’s likely some changes will be required in this area.

What I’m excited about in SQL 2022

The intelligent query processing features in SQL 2022 and tempdb concurrency enhancements are very interesting. There is also some really useful language enhancements like GENERATE_SERIES, DATE_BUCKET, GREATEST and LEAST that I can see myself using. Null handling also gets easier with IS [NOT] DISTINCT FROM. I can’t use any of this for DBA Dash as I want to keep the repository compatible with SQL 2016 for now. The language enhancements will be useful for some of the SaaS databases I manage though once we upgrade.

The ability to failover back and forth between Azure managed instance and SQL 2022 is also a game changer. It certainly takes some of the risk out of the process if you are considering using managed instances.

The feature I’m most excited about? Backup/Restore to S3. If you host SQL instances on AWS you have to figure out how to get your backups to S3. There isn’t a standard way to do this. Many people backup to an EBS volume first then push the files to S3 which isn’t ideal. EBS storage is expensive and the two step process to get the backups to S3 adds time and complexity to the process. The restore process is also slower and more complex as you need to pull the backups from S3 first before you can restore them.

The ability to backup directly to S3 is a huge win. It also has value outside of AWS as there are a number of other S3 compatible storage providers.

How to handle SQL Agent Jobs with Always On Availability Groups?

Posted on March 17, 2021March 17, 2021 by David

Introduction

When you configure your SQL servers for high availability you want the failover to be as seamless as possible. If you use availability groups, mirroring or log shipping as part of your high availability strategy you will need to take care of anything that lives outside the context of your databases. That might include logins, SQL Server agent jobs and ensuring consistent server level configuration across your availability nodes. This article will discuss how to handle SQL server agent jobs.

Not so seamless failover of agent jobs

If you have any SQL agent jobs that run in the context of your user databases these will need special handling to ensure a seamless failover. Ideally Microsoft would have added some improvements to the SQL agent to help with this. Maybe given us agent jobs that can live within the context of a user database instead of msdb?

As it stands you will need to keep your agent jobs in sync between the primary and secondaries and figure out a mechanism to activate the jobs on the new primary following failover and disable them on the old primary.

A Solution

There are a few ways to handle this, but my approach is to run all the jobs in the context of the master DB. I then check if the user DB is accessible and execute the command with a 3 part name. See the script below for an example. This will work for availability groups, mirroring and log shipping.

DECLARE @DB SYSNAME = 'MyDB'
IF DATABASEPROPERTYEX(@DB, 'Updateability') = 'READ_WRITE'
	AND EXISTS(SELECT 1 FROM sys.databases WHERE name=@DB and state = 0)
BEGIN
	PRINT 'Run command'
	--EXEC MyDB.dbo.MyProc
END
ELSE
BEGIN
	PRINT 'Not run: Database is not writeable'
END

DECLARE @DB SYSNAME = 'MyDB'

IF DATABASEPROPERTYEX(@DB, 'Updateability') = 'READ_WRITE'

AND EXISTS(SELECT 1 FROM sys.databases WHERE name=@DB and state = 0)

BEGIN

PRINT 'Run command'

--EXEC MyDB.dbo.MyProc

END

ELSE

BEGIN

PRINT 'Not run: Database is not writeable'

END

With this approach you can create your jobs and have them active on your primary and secondaries. You don’t need to worry about enabling/disabling jobs on failover – the jobs are already running but they only do any work if the database is primary. This makes the failover totally seamless. The downside to this approach is that the jobs will report that they ran successfully even if they didn’t do any work which could cause confusion.

The next problem is keeping the jobs in sync between your servers. You can script out the jobs using SSMS easy enough but this could get tedious if you have a larger number of jobs. I’ve created a powershell script below to help with this which you can use below. You can use this to report if your jobs are out of sync and also for performing a synchronization. See the comments at the top of the script for some usage examples.

Param(
    [Parameter(mandatory=$true)] 
    [string]$ServerA, 
    [Parameter(mandatory=$true)] 
    [string]$ServerB, 
    [Parameter(mandatory=$false)] 
    [System.Array]$ExcludedCategories=@(), #Job categories to exclude from the synchronization process.
    [Parameter(mandatory=$false)] 
    [System.Array]$IncludedCategories=@(), #Job categories to include in the synchronization process.
    [Parameter(mandatory=$false)] 
    [switch]$DoSync #perform a two way synchronization if specified, otherwise run in reporting mode.
)
<# Created: 2017-12-06
   Author:  David Wiseman
  
   Description: Sync agent jobs between two SQL Server instances or check synchronization status.  This is useful if you are using a high availability technology like
   availability groups, log shipping or database mirroring that does not include SQL Agent jobs in the failover.  The script can be used to validate that the jobs are in sync
   or it can synchronize the jobs.  

   Requires SqlServer powershell module:
   Install-Module -Name SqlServer -AllowClobber

   Example1: 
   ./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2"
   Agent jobs will be checked and differences will be reported. 
   
   Example2: 
   ./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2" -DoSync
   Any jobs that don't exist will be copied from SERVER1 to SERVER2 and from SERVER2 to SERVER1.  Any jobs that are different will be copied from the server with the newer version of the job (Based on DateLastModified).  

   Example3: 
   ./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2" -DoSync ExcludedCategories "ADHOC"
   Same as example 2 but jobs in the category "ADHOC" are excluded from synchronization.

   Note: SQL Agent loads SQLPS which isn't compatible with SqlServer module.  A workaround is to run the following powershell in the SQL Agent job.
    $Error.Clear()
    $out = powershell  -NoProfile -Command "C:\Scripts\SyncSQLAgentJobs.ps1" -ServerA "SERVER1" -ServerB "SERVER2"
    if ($Error.Count -gt 0){
    throw $out
    }
    else{
    $out
    }
   This will ensure the job fails if anything is out of sync.  The SQL Server agent account will need appropriate access to msdb database on both instances.

#>
$ErrorActionPreference="stop"
Import-Module SqlServer -DisableNameChecking

$IncludedCategories

$jobs = Get-SqlAgentJob -ServerInstance $ServerA
$ValidationStatus=$true
$errorMsg=""

$jobs | ForEach-Object{
    $jobA = $_
    $jobB = $null
    $scriptA = ""
    $jobA.Script() | ForEach-Object {$scriptA += $_}
    $jobB = Get-SqlAgentJob -ServerInstance $ServerB -Name $jobA.Name -ErrorAction Ignore

    if ($ExcludedCategories.Contains($jobA.Category)){
        "Skipping " + $ServerA + ": " + $jobA.Name + " : Excluded category: " + $jobA.Category
    }
    elseif($jobB -ne $null -and $ExcludedCategories.Contains($jobB.Category)){
        "Skipping " + $ServerB + ": " + $jobB.Name + " : Excluded category: " + $jobB.Category
    }
    elseif($jobA -ne $null -and $IncludedCategories.Count -gt 0 -and (!$IncludedCategories.Contains($jobA.Category))){
        "Skipping " + $ServerA + ": " + $jobA.Name + " : Not included category: " + $jobA.Category
    }
    elseif($jobB -ne $null -and $IncludedCategories.Count -gt 0 -and (!$IncludedCategories.Contains($jobB.Category))){
        "Skipping " + $ServerB + ": " + $jobB.Name + " : Not included category: " + $jobB.Category
    }
    elseif ($jobB -eq $null){
        $ErrorMsg += "Missing on " + $ServerB + ":" + $jobA.Name + "`n"
        "Missing on " + $ServerB + ":" + $jobA.Name
        $ValidationStatus=$false
        if ($DoSync){
            "Creating job " + $jobA.Name + " on " + $ServerB
             Invoke-Sqlcmd -ServerInstance $ServerB -Query $scriptA -DisableVariables -DisableCommands
        }
    }
        else {
       
        $scriptB = ""
        $jobB.Script() | ForEach-Object {$scriptB += $_}
    
        if ($scriptA -eq $scriptB){
            "OK: " + $_.Name
        }
        else {
            if ($jobA.DateLastModified -lt $jobB.DateLastModified){
                $ErrorMsg += "Different (Newer on $ServerB): " + $_.Name + "`n"
                "Different (Newer on $ServerB): " + $_.Name
                $ValidationStatus=$false
                if ($DoSync){
                    "Dropping job " + $jobA.Name + " on " + $ServerA
                     $JobA.Drop()
                     "Creating job " + $jobA.Name + " on " + $ServerA
                     Invoke-Sqlcmd -ServerInstance $ServerA -Query $scriptB -DisableVariables -DisableCommands
                }
            }
            else{
                $ErrorMsg += "Different (Newer on $ServerA): " + $_.Name + "`n"
                "Different (Newer on $ServerA): " + $_.Name
                $ValidationStatus=$false
                if ($DoSync){
                    "Dropping job " + $jobB.Name + " on " + $ServerB
                     $JobB.Drop()
                     "Creating job " + $jobB.Name + " on " + $ServerB
                     Invoke-Sqlcmd -ServerInstance $ServerB -Query $scriptA -DisableVariables -DisableCommands
                }
            }
        }
    }
}

 $missingA = Get-SqlAgentJob -ServerInstance $ServerB | Where-Object {@(Get-SqlAgentJob -ServerInstance $ServerA -Name $_.Name -ErrorAction Ignore).Count -eq 0}
 $missingA | ForEach-Object{
     $jobB = $_
     if ($ExcludedCategories.Contains($jobB.Category)){
        "Skipping " + $ServerB + ": " + $jobB.Name + " : Excluded category: " + $jobB.Category
     }
     else{
         $ErrorMsg += "Missing on " + $ServerA + ":" + $jobB.Name + "`n"
         "Missing on " + $ServerA + ":" + $jobB.Name 
         $ValidationStatus=$false
         if ($DoSync){
                $scriptB = ""
                $jobB.Script() | ForEach-Object {$scriptB += $_}
                "Creating job " + $jobB.Name + " on " + $ServerA
                 Invoke-Sqlcmd -ServerInstance $ServerA -Query $scriptB -DisableVariables -DisableCommands

         }
     }
 }
 if ($ValidationStatus -eq $false -and $DoSync -eq $false){
    $errorMsg = "SQL Agent jobs require synchronization`n" + $errorMsg
    throw $errorMsg
 }

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

Param(

[Parameter(mandatory=$true)]

[string]$ServerA,

[Parameter(mandatory=$true)]

[string]$ServerB,

[Parameter(mandatory=$false)]

[System.Array]$ExcludedCategories=@(), #Job categories to exclude from the synchronization process.

[Parameter(mandatory=$false)]

[System.Array]$IncludedCategories=@(), #Job categories to include in the synchronization process.

[Parameter(mandatory=$false)]

[switch]$DoSync #perform a two way synchronization if specified, otherwise run in reporting mode.

)

<# Created: 2017-12-06

Author: David Wiseman

Description: Sync agent jobs between two SQL Server instances or check synchronization status. This is useful if you are using a high availability technology like

availability groups, log shipping or database mirroring that does not include SQL Agent jobs in the failover. The script can be used to validate that the jobs are in sync

or it can synchronize the jobs.

Requires SqlServer powershell module:

Install-Module -Name SqlServer -AllowClobber

Example1:

./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2"

Agent jobs will be checked and differences will be reported.

Example2:

./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2" -DoSync

Any jobs that don't exist will be copied from SERVER1 to SERVER2 and from SERVER2 to SERVER1. Any jobs that are different will be copied from the server with the newer version of the job (Based on DateLastModified).

Example3:

./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2" -DoSync ExcludedCategories "ADHOC"

Same as example 2 but jobs in the category "ADHOC" are excluded from synchronization.

Note: SQL Agent loads SQLPS which isn't compatible with SqlServer module. A workaround is to run the following powershell in the SQL Agent job.

$Error.Clear()

$out = powershell -NoProfile -Command "C:\Scripts\SyncSQLAgentJobs.ps1" -ServerA "SERVER1" -ServerB "SERVER2"

if ($Error.Count -gt 0){

throw $out

}

else{

$out

}

This will ensure the job fails if anything is out of sync. The SQL Server agent account will need appropriate access to msdb database on both instances.

$ErrorActionPreference="stop"

Import-Module SqlServer -DisableNameChecking

$IncludedCategories

$jobs = Get-SqlAgentJob -ServerInstance $ServerA

$ValidationStatus=$true

$errorMsg=""

$jobs | ForEach-Object{

$jobA = $_

$jobB = $null

$scriptA = ""

$jobA.Script() | ForEach-Object {$scriptA += $_}

$jobB = Get-SqlAgentJob -ServerInstance $ServerB -Name $jobA.Name -ErrorAction Ignore

if ($ExcludedCategories.Contains($jobA.Category)){

"Skipping " + $ServerA + ": " + $jobA.Name + " : Excluded category: " + $jobA.Category

}

elseif($jobB -ne $null -and $ExcludedCategories.Contains($jobB.Category)){

"Skipping " + $ServerB + ": " + $jobB.Name + " : Excluded category: " + $jobB.Category

}

elseif($jobA -ne $null -and $IncludedCategories.Count -gt 0 -and (!$IncludedCategories.Contains($jobA.Category))){

"Skipping " + $ServerA + ": " + $jobA.Name + " : Not included category: " + $jobA.Category

}

elseif($jobB -ne $null -and $IncludedCategories.Count -gt 0 -and (!$IncludedCategories.Contains($jobB.Category))){

"Skipping " + $ServerB + ": " + $jobB.Name + " : Not included category: " + $jobB.Category

}

elseif ($jobB -eq $null){

$ErrorMsg += "Missing on " + $ServerB + ":" + $jobA.Name + "`n"

"Missing on " + $ServerB + ":" + $jobA.Name

$ValidationStatus=$false

if ($DoSync){

"Creating job " + $jobA.Name + " on " + $ServerB

Invoke-Sqlcmd -ServerInstance $ServerB -Query $scriptA -DisableVariables -DisableCommands

}

else {

$scriptB = ""

$jobB.Script() | ForEach-Object {$scriptB += $_}

if ($scriptA -eq $scriptB){

"OK: " + $_.Name

}

else {

if ($jobA.DateLastModified -lt $jobB.DateLastModified){

$ErrorMsg += "Different (Newer on $ServerB): " + $_.Name + "`n"

"Different (Newer on $ServerB): " + $_.Name

$ValidationStatus=$false

if ($DoSync){

"Dropping job " + $jobA.Name + " on " + $ServerA

$JobA.Drop()

"Creating job " + $jobA.Name + " on " + $ServerA

Invoke-Sqlcmd -ServerInstance $ServerA -Query $scriptB -DisableVariables -DisableCommands

}

else{

$ErrorMsg += "Different (Newer on $ServerA): " + $_.Name + "`n"

"Different (Newer on $ServerA): " + $_.Name

$ValidationStatus=$false

if ($DoSync){

"Dropping job " + $jobB.Name + " on " + $ServerB

$JobB.Drop()

"Creating job " + $jobB.Name + " on " + $ServerB

Invoke-Sqlcmd -ServerInstance $ServerB -Query $scriptA -DisableVariables -DisableCommands

}

$missingA = Get-SqlAgentJob -ServerInstance $ServerB | Where-Object {@(Get-SqlAgentJob -ServerInstance $ServerA -Name $_.Name -ErrorAction Ignore).Count -eq 0}

$missingA | ForEach-Object{

$jobB = $_

if ($ExcludedCategories.Contains($jobB.Category)){

"Skipping " + $ServerB + ": " + $jobB.Name + " : Excluded category: " + $jobB.Category

}

else{

$ErrorMsg += "Missing on " + $ServerA + ":" + $jobB.Name + "`n"

"Missing on " + $ServerA + ":" + $jobB.Name

$ValidationStatus=$false

if ($DoSync){

$scriptB = ""

$jobB.Script() | ForEach-Object {$scriptB += $_}

"Creating job " + $jobB.Name + " on " + $ServerA

Invoke-Sqlcmd -ServerInstance $ServerA -Query $scriptB -DisableVariables -DisableCommands

}

if ($ValidationStatus -eq $false -and $DoSync -eq $false){

$errorMsg = "SQL Agent jobs require synchronization`n" + $errorMsg

throw $errorMsg

}

T-SQL Proper Case Function

Posted on September 30, 2007May 25, 2020 by David

This function converts a string to proper case.

e.g.
Convert this: A LONG TIME AGO IN A GALAXY FAR, FAR AWAY
To this: A Long Time Ago In A Galaxy Far, Far Away

Note: It might be better to do this in the application rather than the database and it could be better written as a CLR function. This is useful if you need a T-SQL solution for a one off purpose.

CREATE FUNCTION [dbo].[fProperCase](@Value varchar(8000), @Exceptions varchar(8000),@UCASEWordLength tinyint)
returns varchar(8000)
as
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Purpose: To convert text to Proper Case.
Created By:             David Wiseman
Website:                wisedataman.com
Created:                2005-10-03
Updated:                2006-06-22
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INPUTS:
 
@Value :                This is the text to be converted to Proper Case
@Exceptions:            A list of exceptions to the default Proper Case rules. e.g. |RAM|CPU|HDD|TFT|
                              Without exception list they would display as Ram, Cpu, Hdd and Tft
                              Note the use of the Pipe "|" symbol to separate exceptions.
                              (You can change the @sep variable to something else if you prefer)
@UCASEWordLength: You can specify that words less than a certain length are automatically displayed in UPPERCASE
 
USAGE1:
 
Convert text to ProperCase, without any exceptions
 
select dbo.fProperCase('THIS FUNCTION WAS CREATED BY DAVID WISEMAN',null,null)
>> This Function Was Created By David Wiseman
 
USAGE2:
 
Convert text to Proper Case, with exception for WiseSoft
 
select dbo.fProperCase('THIS FUNCTION WAS CREATED BY DAVID WISEMAN @ WISESOFT','|WiseSoft|',null)
>> This Function Was Created By David Wiseman @ WiseSoft
 
USAGE3:
 
Convert text to Proper Case and default words less than 3 chars to UPPERCASE
 
select dbo.fProperCase('SIMPSON, HJ',null,3)
>> Simpson, HJ
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
begin
      declare @sep char(1) -- Seperator character for exceptions
      declare @i int -- counter
      declare @ProperCaseText varchar(5000) -- Used to build our Proper Case string for Function return
      declare @Word varchar(1000) -- Temporary storage for each word
      declare @IsWhiteSpace as bit -- Used to indicate whitespace character/start of new word
      declare @c char(1) -- Temp storage location for each character
 
      set @Word = ''
      set @i = 1
      set @IsWhiteSpace = 1
      set @ProperCaseText = ''
      set @sep = '|'
 
      -- Set default UPPERCASEWord Length
      if @UCASEWordLength is null set @UCASEWordLength = 1
      -- Convert user input to lower case (This function will UPPERCASE words as required)
      set @Value = LOWER(@Value)
 
      -- Loop while counter is less than text lenth (for each character in...)
      while (@i <= len(@Value)+1)
      begin
 
            -- Get the current character
            set @c = SUBSTRING(@Value,@i,1)
 
            -- If start of new word, UPPERCASE character
            if @IsWhiteSpace = 1 set @c = UPPER(@c)
 
            -- Check if character is white space/symbol (using ascii values)
            set @IsWhiteSpace = case when (ASCII(@c) between 48 and 58) then 0
                                          when (ASCII(@c) between 64 and 90) then 0
                                          when (ASCII(@c) between 96 and 123) then 0
                                          else 1 end
 
            if @IsWhiteSpace = 0
            begin
                  -- Append character to temp @Word variable if not whitespace
                  set @Word = @Word + @c
            end
            else
            begin
                  -- Character is white space/punctuation/symbol which marks the end of our current word.
                  -- If word length is less than or equal to the UPPERCASE word length, convert to upper case.
                  -- e.g. you can specify a @UCASEWordLength of 3 to automatically UPPERCASE all 3 letter words.
                  set @Word = case when len(@Word) <= @UCASEWordLength then UPPER(@Word) else @Word end
 
                  -- Check word against user exceptions list. If exception is found, use the case specified in the exception.
                  -- e.g. WiseSoft, RAM, CPU.
                  -- If word isn't in user exceptions list, check for "known" exceptions.
                  set @Word = case when charindex(@sep + @Word + @sep,@exceptions collate Latin1_General_CI_AS) > 0
                                    then substring(@exceptions,charindex(@sep + @Word + @sep,@exceptions collate Latin1_General_CI_AS)+1,len(@Word))
                                    when @Word = 's' and substring(@Value,@i-2,1) = '''' then 's' -- e.g. Who's
                                    when @Word = 't' and substring(@Value,@i-2,1) = '''' then 't' -- e.g. Don't
                                    when @Word = 'm' and substring(@Value,@i-2,1) = '''' then 'm' -- e.g. I'm
                                    when @Word = 'll' and substring(@Value,@i-3,1) = '''' then 'll' -- e.g. He'll
                                    when @Word = 've' and substring(@Value,@i-3,1) = '''' then 've' -- e.g. Could've
                                    else @Word end
 
                  -- Append the word to the @ProperCaseText along with the whitespace character
                  set @ProperCaseText = @ProperCaseText + @Word + @c
                  -- Reset the Temp @Word variable, ready for a new word
                  set @Word = ''
            end
            -- Increment the counter
            set @i = @i + 1
      end
      return @ProperCaseText
end

100

101

102

103

104

105

106

107

108

109

110

CREATE FUNCTION [dbo].[fProperCase](@Value varchar(8000), @Exceptions varchar(8000),@UCASEWordLength tinyint)

returns varchar(8000)

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Function Purpose: To convert text to Proper Case.

Created By: David Wiseman

Website: wisedataman.com

Created: 2005-10-03

Updated: 2006-06-22

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INPUTS:

@Value : This is the text to be converted to Proper Case

@Exceptions: A list of exceptions to the default Proper Case rules. e.g. |RAM|CPU|HDD|TFT|

Without exception list they would display as Ram, Cpu, Hdd and Tft

Note the use of the Pipe "|" symbol to separate exceptions.

(You can change the @sep variable to something else if you prefer)

@UCASEWordLength: You can specify that words less than a certain length are automatically displayed in UPPERCASE

USAGE1:

Convert text to ProperCase, without any exceptions

select dbo.fProperCase('THIS FUNCTION WAS CREATED BY DAVID WISEMAN',null,null)

>> This Function Was Created By David Wiseman

USAGE2:

Convert text to Proper Case, with exception for WiseSoft

select dbo.fProperCase('THIS FUNCTION WAS CREATED BY DAVID WISEMAN @ WISESOFT','|WiseSoft|',null)

>> This Function Was Created By David Wiseman @ WiseSoft

USAGE3:

Convert text to Proper Case and default words less than 3 chars to UPPERCASE

select dbo.fProperCase('SIMPSON, HJ',null,3)

>> Simpson, HJ

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

begin

declare @sep char(1) -- Seperator character for exceptions

declare @i int -- counter

declare @ProperCaseText varchar(5000) -- Used to build our Proper Case string for Function return

declare @Word varchar(1000) -- Temporary storage for each word

declare @IsWhiteSpace as bit -- Used to indicate whitespace character/start of new word

declare @c char(1) -- Temp storage location for each character

set @Word = ''

set @i = 1

set @IsWhiteSpace = 1

set @ProperCaseText = ''

set @sep = '|'

-- Set default UPPERCASEWord Length

if @UCASEWordLength is null set @UCASEWordLength = 1

-- Convert user input to lower case (This function will UPPERCASE words as required)

set @Value = LOWER(@Value)

-- Loop while counter is less than text lenth (for each character in...)

while (@i <= len(@Value)+1)

begin

-- Get the current character

set @c = SUBSTRING(@Value,@i,1)

-- If start of new word, UPPERCASE character

if @IsWhiteSpace = 1 set @c = UPPER(@c)

-- Check if character is white space/symbol (using ascii values)

set @IsWhiteSpace = case when (ASCII(@c) between 48 and 58) then 0

when (ASCII(@c) between 64 and 90) then 0

when (ASCII(@c) between 96 and 123) then 0

else 1 end

if @IsWhiteSpace = 0

begin

-- Append character to temp @Word variable if not whitespace

set @Word = @Word + @c

end

else

begin

-- Character is white space/punctuation/symbol which marks the end of our current word.

-- If word length is less than or equal to the UPPERCASE word length, convert to upper case.

-- e.g. you can specify a @UCASEWordLength of 3 to automatically UPPERCASE all 3 letter words.

set @Word = case when len(@Word) <= @UCASEWordLength then UPPER(@Word) else @Word end

-- Check word against user exceptions list. If exception is found, use the case specified in the exception.

-- e.g. WiseSoft, RAM, CPU.

-- If word isn't in user exceptions list, check for "known" exceptions.

set @Word = case when charindex(@sep + @Word + @sep,@exceptions collate Latin1_General_CI_AS) > 0

then substring(@exceptions,charindex(@sep + @Word + @sep,@exceptions collate Latin1_General_CI_AS)+1,len(@Word))

when @Word = 's' and substring(@Value,@i-2,1) = '''' then 's' -- e.g. Who's

when @Word = 't' and substring(@Value,@i-2,1) = '''' then 't' -- e.g. Don't

when @Word = 'm' and substring(@Value,@i-2,1) = '''' then 'm' -- e.g. I'm

when @Word = 'll' and substring(@Value,@i-3,1) = '''' then 'll' -- e.g. He'll

when @Word = 've' and substring(@Value,@i-3,1) = '''' then 've' -- e.g. Could've

else @Word end

-- Append the word to the @ProperCaseText along with the whitespace character

set @ProperCaseText = @ProperCaseText + @Word + @c

-- Reset the Temp @Word variable, ready for a new word

set @Word = ''

end

-- Increment the counter

set @i = @i + 1

end

return @ProperCaseText

end