In TSQL, I need to format a string in a predefined format. For eg: SNO STRING FORMAT OUTPUT 1 A5233GFCOP *XXXXX-XXXXX *A5233-GFCOP 2 K92374 /X-000XXXXX /K-00092374 3 H91543987 XXXXXXXXX H91543987 I am trying with FORMATMESSAGE() built in function. For ex: FORMATMESSAGE(‘*%s-%s’,’A5233′…
Oracle SQL Developer – Getting value from cursor only works with one filter (AND / OR) clause
I am attempting to write a query to retrieve a value from multiple databases and write it to a file. In my function I have a statement which returns results from the cursor: When I try to modify the filter to add another filter, the results are Null: Why would the additional filter remove the results? Here is…
student Gradejump in a historical data with the specific date they had gradejump
I have a historical table holding daily student record. I want to get which date they had change of grade i.e from grade 3a to 3b as jump1 and from 3b to 5 jump2 with the relevant date. below is the script- problem with my script I dont want all the rows that there is no jump. “” all I
How to connect sql Database with ado.net in Asp.Net Core Mvc 5.0?
I can not find where am i missing. my code like this ; users.cs : } usersAccessLayer.cs ; appsettings.json And finally i got this error; An unhandled exception occurred while processing the request. NullReferenceException: Object reference not set to an instance of an object. Adonet_Sql.Models.users..ctor() i…
Parse text of json object key value postgresql
I have the following kind of objects maintained in a table column. I have tried parsing this object to get the value for this key “1075852262” which has this value “Event=”13″ Description=”(EMOVIES r8)”……….” The SQL I have written is: The resul…
Select node from xml nodes
there! there is a small xml. I’m selecting data via And result like this But, need to have one more column with the contents of the entire row, like this Answer Use the query method cc type is XML. db<>fiddle
SQL convert rows to one Column
I have a table, Output before pivoting: Expected output: How to do that for three or more rows but always with to one-row column? Answer You can use conditional aggregation: However, I might suggest that you if you want all values in a single row that you just use arrays:
Where column “c” is not null for each group in column “a” – give group “a” a value of 1
Column “C” is a flag indicator. I want to run a query where I can sum column “c” where it is not null. I would like 1 value for each group of column A. For instance group “Test”, would have a total of 1. Group chance has a total of 1. Group “Play” has a total of…
Function to convert char(255) non standard dates into timestamps
I have database with a column of date strings that are in the format YYYY-MM-DDThh:mm:ss.3nZ I would like to transform them into TIMESTAMP so that I can then apply the BETWEEN function to identify rows that fall in a given time window (this being the ultimate goal of the exercise). I can’t just change t…
How to create a Table Type with columns more than 1024 columns
I want to create a table type that should have more than 1024 columns. So I tried to use sparse columns by creating a – SpecialPurposeColumns XML COLUMN_SET as shown below. That did not work. It gave me an error: Incorrect syntax near ‘COLUMN_SET’ Is there any way that I can create a table t…