Daily Coping 17 Jun 2022

Posted on June 17, 2022 by way0utwest

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to spend a few minutes looking at a piece of art and think about what you enjoy about it.

I haven’t ever been a bit art fan. However, I saw an ad for the Van Gogh Experience in 2021 and mentioned that it looked interesting to my wife. She bought tickets and last fall we went. It was cool, and hard to describe, but essentially a movie version of many pieces being drawn on walls and evolving.

I really enjoyed it, and since then, I’ve kept a Van Gogh painting as a background on my desktop, rotating with a family photo or two and my coping calendar.

Right now, I have this piece on my desktop:

tlA9gj4-van-gogh-desktop-wallpaper

This reminds me of Europe, in old time, when we have life going on. People working, a horse and carriage on a daily journey of work, perhaps going to load up the work from people washing clothes in the river.

Perhaps because I do most of the laundry, I’m glad I don’t clean clothes in a river, but at the same time, there is a level of pleasure from accomplishing something manual like this.

I’m also amazed by the brightness of colors and how they paint a picture, despite being very different in similar places. The bricks of the bridge having yellowa, greens, oranges, even a bit of blue. It’s like a vivid reminder of the richness of life. I love the way m imagination creates the details that are blurry in this style of painting. I think I see something else in the water each time I look. Sometimes it’s the plants, sometimes I see fish. Sometimes I think there are more plants than others. Sometimes I can imagine the oil slicks or pollution I see in many rivers on my travels because of boat engines.

I find myself enjoying taking a moment on these paintings, thinking how Van Gogh’s life might be different and similar to mine.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 17 Jun 2022

VS Live Slides and Code

Posted on June 16, 2022 by way0utwest

Thanks to everyone that came to my talk at VS Live today.

I’ve uploaded the slides to the blog. Here are the two decks:

Code for the CI Talk is on my Github. You can git clone from here: https://github.com/way0utwest/usingcitopreventdbproblems.git

The code is structured as:

  • 0 files are setup, numbered 04, 06, 08, and 10. These create objects, add the tsqlt framework and load some data.
  • 20_Standards.sql – Standards based tests
  • 30_logicerrors.sql – The logical CASE statement test
  • 40_tsqlt_sqlinjection – Catching SQL Injection issues with EXEC()
  • 50_preent_alter – I didn’t demo, but how to look for code you don’t want developers to run.
  • 61, 62 63 – Older separation of concerns testing for procs/functions. I haven’t tested this lately, but it should work.

If you have questions, let me know.

Posted in Blog | Tagged , , , | Comments Off on VS Live Slides and Code

Daily Coping 16 Jun 2022

Posted on June 16, 2022 by way0utwest

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to listen to a favorite piece of music from the past.

I woke up the other day and saw a new album from BTS. I put it on and listened. Not that I’m a big BTS fan, but I haven’t heard much of their music, so I just put it on in the background as I worked.

Then I ran across this tip.

For some reason, I first thought about Changes, but 2Pac. It’s a song of life from the 90s, from the perspective of a young black man in the Baltimore/DC area. For some reason I can appreciate the struggle of growing up like this, even though my life has been nowhere near as hard as it is for many people.

However, I believe in changes and becoming better.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 16 Jun 2022

Using a Regular Expression to Detect a Number–#SQLNewBlogger

Posted on June 15, 2022 by way0utwest

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:

2022-06-07 08_23_13-SQLQuery1.sql - ARISTOTLE.SimpleTalk (ARISTOTLE_Steve (58))_ - Microsoft SQL Ser

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:

2022-06-07 08_31_14-SQLQuery1.sql - ARISTOTLE.SimpleTalk (ARISTOTLE_Steve (58))_ - Microsoft SQL Ser

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.

Posted in Blog | Tagged , , | Comments Off on Using a Regular Expression to Detect a Number–#SQLNewBlogger