Voice of the DBA

Tag: T-SQL

  • Finding Strings

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    A quick one today, just looking for strings. I wrote an article on this, so there’s more detail there, but here’s a bit of code you can look through and see what it does.

    CREATE TABLE dbo.MyTable ( mystring VARCHAR(200) );

    GO

    INSERT dbo.MyTable

        ( mystring )

      VALUES

        ( ‘This is a 7’ )

      , ( ‘There is a 7 in this string’ )

      , ( ‘Why must 7 always be here’ )

      , ( ‘No 7s here. JK’ )

      , ( ‘My 7s here’ )

      , ( ‘An 7s here.’ )

      , ( ‘On 7s here.’ );

    GO

    SELECT

        ‘SetPosition’ = SUBSTRING(mystring, 4, 1)

      , ‘FindThe7’ = SUBSTRING(mystring, CHARINDEX(‘7’, mystring), 1)

      FROM

        dbo.MyTable AS mt;

    DROP TABLE dbo.MyTable;

  • Create a Database Master Key–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    One of the first things you need in a SQL Server database in order to implement encryption is a database master key, DMK. This is simple to create, though you need one in each database that will support encryption.

    The syntax is easy, with only really an option to specify a password. There is no name, as there’s a single DMK per database. Set your context to the correct database and end enter:

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Som3thingR3ally$|tr0ng’;

    When you execute this, you’ll just get a result message. At least, if it works you will. The password must conform to the password requirements of your Windows OS, which is good.

    Note: This is a securable code, like the password for a user account. Make sure you store this in a password manager for your organization.

    By default, this is protected by your password as well as the Service Master Key (SMK) on your instance. In practice this doesn’t usually mean much for you, but be aware of this.

    You do need CONTROL permission on the database, though usually I’d expect a db_owner or more permissions to actually create these keys.

    And, of course, back up the key as soon as you can.

    SQLNewBlogger

    This was about 5 minutes work for me. I would guess most new bloggers could read, understand, and produce an explanation of this in 30 minutes.

    References

    CREATE MASTER KEY – https://msdn.microsoft.com/en-us/library/ms174382.aspx

    Create a Database Master Key – https://msdn.microsoft.com/en-us/library/aa337551.aspx

  • CONVERT and HEX

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    In working through the Advent of Code and solving some of the problems in SQL, I found that I needed to take hex values and convert them to strings. In other words, I had a value like this:

    select @hex = 0x3c044139f4fe36d7df0f4e87f948fc52

    and I needed to determine if the first few characters (5 or 6), were 0s. In other words, I wanted to look at this part of the data above as a string.

    3c044

    I thought this would be simple. I tried this

    select @value = CAST( @hex as varchar(50))

    That’s my default, as it reads nicely. However the returned this:

    <A9ôþ6×ßN‡ùHüR

    That’s strange. I then tried CONVERT:

    select @value = convert( varchar(50), @hex)

    I got the same result. Why am I not getting the same value as a string? I looked at a few other code samples from others, and they looked the same, so I checked the documentation for CONVERT. I saw this:

    Binary Styles: When expression is binary(n), varbinary(n), char(n), or varchar(n), style can be one of the values shown in the following table. Style values that are not listed in the table return an error.

    Under the table, the information for 1 or 2 as a style has this:

    If the data_type is a binary type, the expression must be a character expression. The expression must be composed of an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). If the style is set to 1 the characters 0x must be the first two characters in the expression. If the expression contains an odd number of characters or if any of the characters are invalid an error is raised.

    …

    The characters 0x will be added to the left of the converted result for style 1.

    All of that essentially means that if I use the default, 0, or have nothing, I get the binary data converted to to the binary bytes in ASCII. If I use 1 or 2, I get the string. Here’s a shot of the difference:

    2016-02-02 11_00_14-Settings

    Two lessons. First, learn the data types and how they convert. Second, read the documentation carefully when things don’t work as expected.

  • Quick Tests–Function Returns

    I ran across a neat piece of code recently from Gail Shaw. She answered a question on returning the base path from a path in a string. Meaning if I had this string:

    c:\Users\Sjones\Documents\text.txt

    I’d want to return this:

    c:\Users\Sjones\Documents

    Her code looked like this, which is a nice, simple, elegant way of finding the path, no matter how many backslashes.

    LEFT(@FullPath, LEN(@fullpath) – CHARINDEX(‘\’, REVERSE(@fullpath)))

    Of course, you can easily add the last backslash with a slight change to the math.

    However I wanted to add some tests. Does this really work? What if I don’t have a backslash? I thought the best way to do this was with a few tSQLt tests, which I quickly built. The entire process was 5-10 minutes, which isn’t a lot longer than if I had been running random tests myself with a variety of strings.

    The advantage of tests is that if I come up with a new case, or another potential bug, I copy the test over, change the string and I have a new test, plus all the regressions. I’m not depending on my memory to run the test cases.

    I first put the code in a function, which makes it easier to test.

    CREATE FUNCTION GetParentPath
      ( @fullpath VARCHAR(4000)
      )
    RETURNS varchar(4000)
    AS
    BEGIN
      RETURN LEFT(@FullPath, LEN(@fullpath) – CHARINDEX(‘\’, REVERSE(@fullpath)))
    END

    Here’s my base test:

    EXEC tsqlt.NewTestClass ‘StringTests’;
    go
    CREATE PROCEDURE [StringTests].[test simple path with one backslash]
    AS
    BEGIN
    — Assemble
    DECLARE @input VARCHAR(4000) = ‘c:\myfile.txt’
       , @expected VARCHAR(4000) = ‘c:’
       , @actual VARCHAR(4000)

    — Assert
    EXEC @actual = dbo.GetParentPath
      @fullpath = @input

    — Assert
    EXEC tsqlt.AssertEquals
      @Expected = @expected
    , @Actual = @actual
    , @Message = N’Incorrect Path’
    END
    GO

    I can easily copy this and add new inputs with different paths, and matchout outputs, to test new cases. For example, my first cut produced five tests for these inputs:

    • c:\myfile.txt
    • c:\
    • c:
    • c:\Documents\myfile.txt
    • c:\Users\sjones\Documents\myfile.txt

    There are certainly other tests, but this 5-10 minutes of work gives me repeatable testing, and if I needed to include this function in a larger project, I already have a series of tests that can be run in my CI process.

    What’s more, if I replaced this with a CLR function, such as something with SQL#, I could still use these tests.