GoldenNugets – Page 5 – Just trying to help someone out there :)

Strip text and extract numeric value from string using TSQL

Today I had an issue that someone changed format of a string pattern our SQL was expecting. I came across a function written by George Mastros that addressed my SQL needs. Long story short we have a string with text then a number and some more text. What we need is to extract the numeric value from this string. The original code someone wrote at work had lots of replace this and replace that plus substring brute force approach, but if the string now did not include bracket then we had a problem since replace was looking for it. So I just marginally altered George’s code to suit my needs to fish out positive numbers from a string that contains only on multi digit number with a decimal place:

left(SubString(stringfield, PatIndex(‘%[0-9.]%’, stringfield), 8000), PatIndex(‘%[^0-9.]%’, SubString(stringfield, PatIndex(‘%[0-9.]%’, stringfield), 8000) + ‘X’)-1)

This now will extract numbers such 3.00 or 45 from a string. Of course this still is limited as the pattern can’t include more than one number but in my case that is sufficient.

Cheers!

Time services fixup on Windows

I keep having to deal with this issue every now and then so I figured I might as well record what commands are needed to fix it up 99% of the time:

w32tm /config /manualpeerlist:"time.nist.gov time.windows.com time-nw.nist.gov time-a.nist.gov time-b.nist.gov time-a.timefreq.bldrdoc.gov time-b.timefreq.bldrdoc.gov time-c.timefreq.bldrdoc.gov utcnist.colorado.edu" /syncfromflags:manual /update

and then (note you can replace the server list with your own – these should work from anywhere but may not be suitable for intranets for instance)

w32tm /resync

Simple but fixes up lots of little time related issues.

NOTE

Just a reminder to people who host their virtual machines (VM) that some of time related issues are caused by VM picking up time synchronization from the host such as Hyper-V. In that case the above needs to be applied to the Hyper-V server as well.

RSA format key file from private key file

Came across a small issue when trying to use keys from iPad to ssh to Ubuntu server. The application refused to do anything (2 different apps!) and one said that it needed an RSA format key file. After looking at the file formats and reading up on openssl I found that you can convert openssl file format files generated for use on Linux using the following command
openssl rsa -in server.key -out server_new.key
Simple but on the spot it makes you pause in your track.

Hope this helps someone.