I have column called “CustomerId” with value “1_Nissan_028” and “2_Ferrari_035”. I would like to extract “Nissan” or “Ferrari” as “CustomerName”. CustomerName is located in middle of CustomerId and lenght varies. Following SQL query return values like “Nissan_” or “Ferrar”. How to write SQL statement? Answer Assuming that the value is always the 2nd delimited value, you can use STRING_SPLIT and
Tag: azure-sql-database
Finding latest timestamp per value across columns and rows
I have a database table with rows like Where ID is the non-unique identifier for the rows and will be used to filter the initial dataset. Fields s1 to s6 contain values and columns ts1 to ts6 contain their timestamps. And I need to find the latest (or first) timestamp per “s” value. The “s” values are not unique and
Fill NULL and get distinct record
I am ingesting data from two different source (source A and source B). Although data may be coming from two different sources, there is a possibility that an ID for a record may exist in both sources. The problem is, there are cases when one source is more complete than the other. In the example below, Source A has a
Executing stored procedure via linked server through trigger
I have created a linked server on a SQL Server connecting to an Azure SQL datawarehouse. When I execute a stored procedure of SQL DW via SSMS as below, it is getting executed as expected. But when I try to have that stored procedure executed as a part of trigger definition And when I try to insert a record into
Azure hosted SQL: show all fields in a table as a saved View
Probably a simple answer, but assume I have a table with 3 columns: I create a new view It works. I save the view, SSMS automatically changes the saved version of the view to say: How do I keep the saved version of the view to include all columns in tbl rather than explicitly identifying each column? Answer You can
Is there an Azure SQL Database equivalent to MySQL’s “CHECKSUM TABLE”, and should it be used to check if two tables are identical?
I was looking for a way to check if two tables in the same database are identical (both contents and schema), and came over this question. The top answer for the question uses (from my understanding) a MySQL specific. When trying to run the code in an Azure SQL Database I get this error: Incorrect syntax near the keyword ‘TABLE’..
Take last value in sequence
I am trying to insert data into my target table from my source table where in the target table I have an additional column called SaleTo. SaleTo = the SaleFrom based on the MAX SaleSequence. Example of the source table: SaleNo SaleFrom SaleSequence 1 Alabama 2 1 Minnesota 1 1 Virginia 3 Example of target table: SaleNo SaleFrom SaleSequence SaleTo
Unable to log into Azure SQL database in Azure Portal using AAD Global Admin (member type) account
I am Global Administrator of the Azure subscription I own. Steps: I logged into Azure Portal using my credentials. I created an Azure SQL database (including initiating a new Azure SQL Server instance). I set the database security to permit both SQL Login and AAD Auth. I set my AAD account to be SQL Admin of the server. In the
Multiple location to reference location dimension
I am trying to map values associated to my location dimension – dimension seen below: LocationID Country State City Zip Code Longitude Latitude 1 USA NY Manhattan 2 USA NY Yonkers 3 USA NY Buffalo I am receiving transaction data where multiple regions may be identified as a value. For example, I will get a value for city as such:
Move values in different columns to one row
I have the following table: With this sample data: Id is my primary key, and this should be UNIQUE in the table. This is possible for all of my rows, if I simply move all values up to one row in the above example. I only need to make this work when data looks like it is in the above