Month: October 2021

T-SQL Tuesday #143 – Short code examples

Time to read: ~ 2 minutes

Words: 328

Welcome to T-SQL Tuesday, the monthly blog post invitational where we’re given a topic and asked to write about it.

This month we have John McCormack (Blog | Twitter) asking, “What are your go-to handy scripts“?

For this post, I’m going to break these down into different languages.

SQL

I once had the annoyingly complex T-SQL to change MS format time into a human-readable format memorised. 

SELECT
    time_MS_format = [TimeMSFormat],
    converted_time = '2021-10-12 ' + 
    STUFF(
        STUFF(
            RIGHT('000000' + X.TimeMSFormat, 6), 3, 0, ':'
        ), 6, 0, ':'
    )
FROM (VALUES
     ('00000')
   , ('00500')
   , ('01000')
   , ('10000')
   , ('10500')
   , ('100000')
   , ('100500')
   , ('115500')
   , ('120000')
) X ([TimeMSFormat]);

Then I read a blog post from Kenneth Fisher (Blog | Twitter) about the in-built msdb database function dbo.agent_datetime.

SELECT
    time_MS_format = [TimeMSFormat],
    new_function = msdb.dbo.agent_datetime(20211012, X.TimeMSFormat)
FROM (VALUES
     ('00000')
   , ('00500')
   , ('01000')
   , ('10000')
   , ('10500')
   , ('100000')
   , ('100500')
   , ('115500')
   , ('120000')
) X ([TimeMSFormat]);

If I run sp_helptext on that function, it reminds me of that Andy Mallon (Blog | Twitter) post.

It would be more performant if I stripped that function and used the code directly but the code is too handy to use for the infrequent times I need it.

PowerShell

I’ve talked before about using ConvertTo-SqlSelect in a blog post before and I still use that function alot!

ConvertTo-SQLSelect

Another short piece of code that I use is more for formatting than anything else. You can populate a variable with an array of properties names. Select-Object can use this variable to return information.

 $Properties = 'SqlInstance', @{Name = 'DatabaseName'; Expression = {$_.Name}}, 'Status', 'RecoveryModel', 'Owner'

Get-DbaDatabase -SqlInstance localhost -SqlCredential $Cred | Select $Properties

A useful snipper for reporting is to use a combination of Sort-Object and the Format-* commands with the -GroupBy parameter.

Get-DbaDatabase -SqlInstance localhost -SqlCredential $Cred |
    Select $Properties |
    Sort-Object RecoveryModel |
    Format-Table -GroupBy RecoveryModel

Sin é

When I sit down and write this post, I realise that I don’t have a lot of handy scripts. Either I re-write things constantly (that’s likely), or I don’t know enough yet (also likely). I should fix that.

The Surprising Working of TrimEnd

Time to read: ~ 2 minutes

Words: 397

A couple of days ago, I was running some unit tests across a piece of PowerShell code for work and a test was failing where I didn’t expect it to.

After realising that the issue was with the workings of TrimEnd and my thoughts on how TrimEnd works (versus how it actually works), I wondered if it was just me being a bit stupid.

So I put a poll up on Twitter, and I’m not alone! 60% of the people answering the poll had the wrong idea as well.

Let’s have some code to show what we mean.

'Shanes_sqlserver'

Incorrect Ideas

The vast majority of code that I have seen out in the wild has strings as the inner portion of TrimEnd

'Shanes_sqlserver'.TrimEnd('sqlserver')


The code works how I thought that it would, removing the “sqlserver” portion of the string at the end. Now, let’s try it again and remove the underscore as well.

'Shanes_sqlserver'.TrimEnd('_sqlserver')



See! Where has my “s” and “e” gone?!

Let’s look at the overload definitions for TrimEnd by running the code without the brackets after the method.

'Shanes_sqlserver'.TrimEnd


No overload definition takes a string; they either take a char or an array of chars. Is that what’s happening here?

# Takes an array of chars
'Shanes_sqlserver'.TrimEnd('_', 's', 'q', 'l', 'e', 'r', 'v')

# Turns a string into an array of chars
'Shanes_sqlserver'.TrimEnd('_sqlerv')

# Order doesn't matter either
'Shanes_sqlserver'.TrimEnd('vrelqs_')

A New Way of Thinking

So TrimEnd takes the characters that we provide inside the method and removes them from the end until it reaches the first non-matching character.

This example explains why our first example, with TrimEnd('sqlserver'), removes everything up to the underscore.

'Shanes_sqlserver'.TrimEnd('sqlserver')
# -----^ First non-matching character (_)


However, when we include the underscore, the first non-matching character shuffles back.

'Shanes_sqlserver'.TrimEnd('_sqlserver') 
# --^ First non-matching character (n)

Initial Problem

Now that we have a new understanding of how TrimEnd works, how can we remove the “_sqlserver” part of the string?

Split it in two.

'Shanes_sqlserver'.TrimEnd('sqlserver').TrimEnd('_')
# -----^  First non-matching character (_)
# ----^  First non-matching character after first TrimEnd (s)

This rewrite works for us since we have a defined character that acts as a stop-gap. If that stop-gap isn’t possible, then -replace may be our best option.

'Shanes_sqlserver' -replace '_sqlserver'

Always good to get a better understanding of PowerShell. If my tests catch more of these misunderstandings that I can learn from, then I’m OK with that!

Exit mobile version
%%footer%%