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.
