Migrate SQLPrompt Snippets to VSCode

-
 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr
Post a Comment

SQL ISNUMERIC() function

-
Imagine you have a column of data which is supposed to contain numbers, but you don't know which ones are numbers. Imaging you also live in a world where SQL Server 2012 and above don't exist. Now imagine you stumble across the function ISNUMERIC one day and think to yourself "huzzah! My worries are over!". Now imagine you put that check in to your code, push it to production, and low and behold, it doesn't do what you expect.

This is documented elsewhere as well but ISNUMERIC might not do what you expect. It includes characters which aren't strictly speaking numeric values. For instance, it contains several currency signs. It allows some white space characters, and it allows commas, pluses, minuses and more. Consider the following.

select 
    Idx = num,
    Chr = char(num),
    IsNum = isnumeric(char(num)),
    IsNum_e0 = isnumeric(char(num) + 'e0')
from (select top 255 num = row_number() over (order by (select null))
      from sys.all_objects) a
order by isnumeric(char(num)) desc, num 
 
You can see all the characters (from the first 255 ascii characters) which it will consider to be "numeric". It's also worth noting that this is not a comprehensive list of characters though, as I'll illustrate in a way which improves the functionality of isnumeric in many cases. the trick is to add 'e0' to the end of the string you're converting. So 123 becomes 123e0. Since this is scientific notation, it's considered a numeric value; and rightfully so. but "$\e0" is decidedly NOT numeric. In the statement above, I included a "e0" suffixed column as well as you can see it cuts out everything that's not a number.

So remember ISNUMERIC is not quite what you think, but if you're expecting a run-of-the-mill integer, float, or decimal value, appending "e0" to the end can get you a long way.

Comments

Post a Comment

Popular posts from this blog

Migrate SQLPrompt Snippets to VSCode

-
 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr
Read more

Fixing Git "Unexpected Disconnect while reading sideband packet"

-
 I ran into an issue the other day (which my co-worker just ran into as well) when doing a git push, where it fails, saying "RPC failed; HTTP 500 curl 22 The Requested URL returned error: 500 send-pack: unexpected disconnect while reading sideband packet". I found almost no useful articles on the matter; most just talked about adding tracing flags or saying "your internet sucks", neither of which addressed the issue. What fixed it for me was setting the http.postBuffer size to something very large, like git config --global http.postBuffer 157286400 This allowed larger files/repositories or something like that to be posted successfully.
Read more

Left-Padding Zeroes

-
 I recently ran across a case where a time value was stored as an Int, with the idea that the first two numbers are the hours, second two the minutes, and third two the seconds. This is all well and good if the hours are double digits (e.g. 144415), as you can just split the string every two characters. If the hours are single digit (e.g. 93251) doing that won't give you the right result. You'll wind up splitting out "93", "25", 1" when what you really want is "09", "32", "51". You also run into this stuff sometimes when you're trying to left-pad a string for a year, month, or date to a pretty format. None of these cases are particularly compelling, and there are probably other solutions for these sorts of issues, but what I DO want to share with you, is a couple ways you can left-pad a string with characters.  FWIW, I've tried doing left-padding with the FORMAT function, and maybe I just suck with it, but I haven&#
Read more