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

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't found a way I can pass it a format string with will left-pad zeroes. So without further adieu, here are a couple ways I found to left-pad integers. The same applies for strings as well, I just wanted to draw attention to the integer case.


declare @IntTime int = 84833

select
    -- Generate n zeroes, where n is the maximum length of the desired string. In this case, 6. The concatenate @IntTime (making a string of length n + len(@IntTime), where n is the desired total length)
    -- Then take the right n characters. This will grab however many characters are in @IntTime, left padded with the remaining zerose.
    right(replicate('0', 6) + cast(@IntTime as varchar(6)), 6), 
    -- Use the rarely useful STR() function to left-pad @IntTime with n spaces (You MUST pass n explicitly otherwise STR() uses a default number of spaces).
    -- Then replace all the spaces with the character you want to fill with.
    -- Note, this won't work if the value you're padding contains spaces (i.e. if it's a string)
    replace(str(@IntTime, 6), ' ', '0'),
    -- Concatenate n - len(@IntTime) zeroes before @IntTime
    concat(replicate('0', 6 - len(@IntTime)), @IntTime)

Comments

Post a Comment

Popular posts from this blog

Master Data Services on Windows 10

-
I just spent several hours trying to figure this out, so I felt it was worth posting here too. If you are installing Master Data Services on Windows 10, there are a bunch of  IIS Web Application Requirements  you have to satisfy in IIS, or the Master Data Services Configuration Manager will say you're missing all kinds of services and roles. Most of the documentation relates to using Windows Server, since that's usually where SQL is running. But for a localhost instance you might have for testing, it's probably not. And in my case, it's on Windows 10 home   edition . That last fact is what threw me most because even after enabling all the features I could find on the list in the link above, it still didn't work. That's because by default, you need Windows 10 professional edition  to use Windows Authentication, and MDS requires Windows Authentication enabled for IIS to work. So you can either upgrade to Pro or manually enable that feature through the command ...
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

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