Tag: T-SQL

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

I had a customer recently that was looking to work with Data Masker for SQL Server and had questions about how to handle some situations. In this case, they needed to detect a number type in a field that was overloaded with multiple types of data. Here’s an example of what they had in their “string” (varchar) field. Look at the stringvalue column below:

If the string was a “nnn nnn nnnn” number value, then they wanted to change it. If it had other values, then leave it alone. This is really a query problem and a WHERE clause to structure.

One would think this is where you use ISNUMERIC() and try that. If I run this, I get zero rows back.

SELECT d.stringvalue
FROM dbo.ddmdemo AS d
WHERE ISNUMERIC(d.stringvalue) = 1

This isn’t really a number, as the sequence has spaces. What if we try this:

SELECT d.stringvalue
FROM dbo.ddmdemo AS d
WHERE ISNUMERIC(REPLACE(' ', '', d.stringvalue)) = 1

It also returns no values.

Really, this appears to really be a regular expression type of query, so I could do this, using LIKE.

SELECT *
FROM dbo.ddmdemo AS d
WHERE d.stringvalue LIKE '[0-9]%'

That, however, gives me two rows in this set of data. I see these results:

The reason is that I am matching the first character only. The argument is a pattern and using square brackets implies a single character in a range. Since there are a lot of different patterns, and the “234223 Test” matches that, I ought to be more specific.

This particular pattern from the customer is 3 numbers, space, 3 numbers, space, 4 numbers. Anything else is non matching. Since there could be trailing spaces, I’d really want this:

SELECT d.stringvalue
FROM dbo.ddmdemo AS d
WHERE d.stringvalue LIKE '[0-9][0-9][0-9] [0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'

This returns my single row. It would match any row that is of the pattern “nnn nnn nnnn” where n is a numerical value from 0-9.

There are other considerations here, and certainly this is likely to be a complex set of masking rules, but this shows a relatively simple way to detect a numerical pattern in a string.

SQL New Blogger

This was an interesting case. I initially thought  LIKE and an expression, but thought maybe there was a quicker way with isnumeric(). I didn’t find one, so I explained that and then the way that did work for me.

To me, this gives someone who glances at my blog a bit of insight into how I think and what I considered. This might be how they think, or someone on their team thinks. This might get me an interview.

Write about the problems you solve and how/why you do it.

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

It’s Memorial Day in the US. A holiday, though I’m off on a trip to Germany today.

I wanted to have a fun Memorial Day Question of the Day today, and I decided to write some code to calculate Memorial Day. This post looks at how the code works.

The Algorithm

Memorial day is always the last Monday in May. For me, I decided to find all the Mondays in May and then take the last one.  I started with a tally table to get a list of days. In any given year, we can find the first day of the year with this code:

DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0)

Now I use a DATEADD with my tally table to find the first 200 days of the year.  The end of May will always fall in this number of days. Here is the code for a list of the first 200 days of the current year:

WITH myTally (n)
AS ( SELECT n = ROW_NUMBER () OVER (ORDER BY
                                       (SELECT NULL))
      FROM
        ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a (n)
        CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b (n)
        CROSS JOIN ( VALUES (1), (2)) c (n) )
    , cteCurrYearDates (myDate)
AS ( SELECT DATEADD (DAY, n, DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0))
      FROM myTally)
    , cteMay (Mondays)

Once I have this, I now need to get the Mondays in May. I can use the DATEPART and MONTH functions to find this. Actually, I ought to use DATEPART to be consistent here, but my habit is MONTH() for the month.

I also need to set the DATEFIRST for this code, otherwise the day of the week will be inconsistent. Here’s the code for this:

SET DATEFIRST 7;
WITH myTally (n)
AS ( SELECT n = ROW_NUMBER () OVER (ORDER BY
                                       (SELECT NULL))
      FROM
        ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a (n)
        CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b (n)
        CROSS JOIN ( VALUES (1), (2)) c (n) )
    , cteCurrYearDates (myDate)
AS ( SELECT DATEADD (DAY, n, DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0))
      FROM myTally)
    , cteMay (Mondays)
AS ( SELECT cteCurrYearDates.myDate
      FROM cteCurrYearDates
      WHERE
        DATEPART (WEEKDAY, cteCurrYearDates.myDate) = 2
        AND MONTH(cteCurrYearDates.myDate) = 5
)

This gives me a list of Mondays in May for the current year. I want the last one, which isn’t easy to do in a result set. However, I can get the first one with a TOP 1 limit. The easy way to get the last one is reverse the order of the rows and then take the first one. I do this with an ORDER BY.

Here’s the complete code:

SET DATEFIRST 7;
WITH myTally (n)
AS ( SELECT n = ROW_NUMBER () OVER (ORDER BY
                                       (SELECT NULL))
      FROM
        ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a (n)
        CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b (n)
        CROSS JOIN ( VALUES (1), (2)) c (n) )
    , cteCurrYearDates (myDate)
AS ( SELECT DATEADD (DAY, n, DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0))
      FROM myTally)
    , cteMay (Mondays)
AS ( SELECT cteCurrYearDates.myDate
      FROM cteCurrYearDates
      WHERE
        DATEPART (WEEKDAY, cteCurrYearDates.myDate) = 2
        AND MONTH(cteCurrYearDates.myDate) = 5
)
SELECT --TOP 1
        Mondays
FROM cteMay
ORDER BY cteMay.Mondays DESC;

Now I can get Memorial Day for the current year.

SQL New Blogger

This is a great example of breaking down an algorithm and explaining it to the reader. If you write T-SQL code for a living, you might write a series of posts on how you solve various problems in T-SQL and explain the process. Link to places where you learn, and show some results that give a feeling for how you built the code.

This took about 10 minutes to write once I’d built all the code. I didn’t go into details with individual result sets, but you could easily do that to show how the code works.