I have a multi-part SDO_GEOMETRY that I can represent as JSON (Oracle 21c): As an experiment, I want to find a way to generate rows from the JSON: Each row is a vertex of the geometry. Question: In an SQL query, is there a way to generate rows for each of the JSON vertices? I’m aware that SDO_GEOMETRY has a
Tag: parsing
How to parse a string and get the value after “=” character
So I have string that should contains “Object.Name” once in a row , if I see it ,I have to get the value after “=” character. If doesn’t match it anywhere in the string i should move hardcoded value. Here is example of the string: I know that I should use case for that but doesn’t know how to proceed
JSON column parse in SQL
one of the columns in my database contains an imported JSON file. Format of JSON: There are hundreds of objects with taskId (3271012,3261201,…) how can i parse this tasks from one column to multiple rows? expected output: Answer First you can get all the “data” items as key-value pairs with OPENJSON and then extract the values of interest The same
Using SSIS OR T-SQL Split a column of quoted & unquoted comma separated values into multiple columns
I have comma separated data in a column named C0. The data in C0 looks like this: C0 “Pacey LLC.”,213830ZZ,11/1/2017,11/1/2017,”297,311.74″,”2,371.40″,0.00,”1,325.18″,0.00,42.22,”123,986.56″ Mike The Miker,9814140VCD,12/1/2018,12/1/2018,”3,917,751.99″,”419,743.54″,”36,642.66″,”344,090.43″,0.00,10.00,”2,434,671.06″ And I want it to end up like this: F1 F1 F3 F4 F5 F6 F7 F8 F9 F10 F11 “Pacey LLC.” 213830ZZ 11/1/2017 11/1/2017 297,311.74 2,371.40 0.00 1,325.18 0.00 42.22 123,986.56 Mike The Miker 9814140VCD 12/1/2018
Why is SQL Server Full Text Search indexing SCR or SUR acronym followed by a number, together?
I discovered a very odd behavior of SQL Server Full Text Search which is indexing SUR, SCR and possibly some other acronyms, together with a number following it – as “Exact Match”. keyword group_id phrase_id occurrence special_term display_term expansion_type source_term s u r 1 2 3 4 5 1 0 1 Exact Match sur 12345 0 SUR 12345 n n
How to create a SQL function to parse multiple xml formatted strings?
I am trying to parse a price from a price column in SQL. I have already create a function that will parse the price but it only works if “all” price strings are the same on all items. I realize now that I need a more robust function that can parse out multiple price configuration. Here is my simple SQL
Extract partial data from a column using SQL (and maybe regex)
I’m working with SQL in MS Access. One of the columns that I import from Excel has date and time in an odd format as below: Jan 12 2021 07:55:14 AM PST MS Access doesn’t recognize this as date and …
IBM DB2 CAST AS VARCHAR versus Python Pandas to_datetime Function
I have the line in a SQL file hitting an IBM DB2 database. For various reasons, I have to convert to VARCHAR, so leaving out the CAST is not an option. The problem is that this casting is choosing a very poor format. The result comes out like this: 2020-06-09-13.15.00.000000. We have the four-digit year with century, month, day of
fetch the data from array of objects sql BigQuery
I need to fetch key value pairs from the second object in array. Also, need to create new columns with the fetched data. I am only interested in the second object, some arrays have 3 objects, some have 4 etc. The data looks like this: The desired output: Answer Below is for BigQuery Standard SQL if applied to sample data
fetch key value pairs from array objects in sql BigQuery
I need to parse the column mapping and fetch the key value pairs from the second object in array. I would like to create new columns with the fetched data. The data looks like this: The desired output: Answer Below is one of the approaches (BigQuery Standard SQL) if to apply above to sample data from your question – result