Convert GMT Literal Time to Unix Seconds in SQL Server

CAST(DATEDIFF(SECOND,{d '1970-01-01'}, (CONVERT(VARCHAR,DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()),[LL_DateTime]),126)+'Z')) as BIGINT) as upload_time,

Remove End Numeric from String Column in SQL Server

select SUBSTRING('VOP1', 0, PATINDEX('%[0-9]%', 'VOP1'));
output: VOP


For HiveQL,
split(col_name,'_')[1]

Replace Tab to Space in XML field of SQL Server

REPLACE(CAST([PassiveDataXML] AS NVARCHAR(MAX)), '\t', ' ') AS PASSIVEDATAXML

1. ERROR: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion of one or more characters from XML to target collation impossible

you have some characters in your XML document that cannot be stored in that varchar column. Either the collation for that column is incorrect, or you need more flexibility in the value that you can store and it should be an nvarchar column instead.

2. ERROR: Target string size is too small to represent the XML instance

NVARCHAR(MAX)

Reference:
1. XML, String converting in SQL Server
https://www.simple-talk.com/sql/database-administration/converting-string-data-to-xml-and-xml-to-string-data/

Query XML file in MS SQL Server

1. create a table with XML field

CREATE TABLE Stores

(

  StoreID INT PRIMARY KEY,

  Survey_untyped XML

);

 INSERT INTO Stores

 VALUES

 (

   292,

  '<StoreSurvey>

    <AnnualSales>800000</AnnualSales>

    <AnnualRevenue>80000</AnnualRevenue>

    <BankName>United Security</BankName>

    <BusinessType>BM</BusinessType>

    <YearOpened>1996</YearOpened>

    <Specialty>Mountain</Specialty>

    <SquareFeet>21000</SquareFeet>

    <Brands>2</Brands>

    <Internet>ISDN</Internet>

    <NumberEmployees>13</NumberEmployees>

    <Products Type="Bikes">

      <Product>Mountain</Product>

      <Product>Road</Product>

      <Product>Racing</Product>

    </Products>

    <Products Type="Clothes">

      <Product>Jerseys</Product>

      <Product>Jackets</Product>

      <Product>Shorts</Product>

    </Products>

  </StoreSurvey>'

);

2. If you want to return a specific element and its child elements, you can do so by referencing one of its attributes.

SELECT

  Survey_untyped.query('/StoreSurvey/Products[@Type="Bikes"]')

    AS BikeProducts

FROM

  Stores;

Reference:

1. https://www.simple-talk.com/sql/learn-sql-server/the-xml-methods-in-sql-server/

Using Oracle SQL Developer with MS SQL

1. Download a JDBC driver for MS-SQL.

2. Load jtds to SQL Developer. Perference->Database->Third Party JDBC Drivers.
Once you load the jtds-1.2 jar file you should see both SQL Server and it's aging uncle Sybase added to your list of options when creating a new database connection. 

3. Setup Database Connections
Select the SQLServer tab, Set "Port" as "1433;instance=instance_name"


Reference:
1. http://thinkoracle.blogspot.ca/2010/09/using-oracle-sql-developer-with-ms-sql.html