I’m using MS SQL Server 2019 I have a string in a table (tblJobCosts) that has its own ID like this: How do I update the value stored in the brackets based on the value from another table? The end result would look like this: I thought I could incorporate a SELECT with a REPLACE but this does not seem
Tag: sql-server-2019
How to split a string which contains delimiter into multiple rows using OPENJSON function
I am trying to split the below string using OPENJSON function in SQL Server 2019: But the result is returned as: I want to display it as this: Thanks Answer This is where my thinking brings me. Note: Unexpected results of the “‘s are NOT balanced. Example or dbFiddle Results
Loop through table and update a specific column
I have the following table: Id Category 1 some thing 2 value This table contains a lot of rows and what I’m trying to do is to update all the Category values to change every first letter to caps. For example, some thing should be Some Thing. At the moment this is what I have: But there are two problems,
How to dynamically write OR clause in Contains query?
Is there a way to generate the second part of this SQL query (OR clauses) from a string split? String split values come from : Answer You can split the string using STRING_SPLIT(), then rebuild it using STRING_AGG(), and simply pass the resulting string into CONTAINS: Or even simpler: In both cases, @SearchPhrase looks like this:
Does Adding Indexes speed up String Wildcard % searches?
We are conducting a wildcard search on a database table with column string. Does creating a non-clustered index on columns help with wildcard searches? Will this improve performance? Proposed Index: for this query Currently using Microsoft SQL Server 2019. Answer Creating a normal index will not help(*), but a full-text index will, though you would have to change your query
Date in where clause returning 0 rows
I am trying to get data as follows WHERE DateCommande = ‘2018-09-05’ but it doesn’t work on my computer. Returns rows, including rows that show 2018-09-05 as the datetime value in a datetime column. However, on SSMS on my computer, if I add a WHERE clause, the query returns 0 rows: Answer yyyy-mm-dd hh:mm:ss.000 is not a language- and regional
Create dynamic list of tables based on list with wildcards
So I have a list of tables that I want (with wildcards) That gives me Now what I need is a list of all tables mentioned in the table config.datalist So I want a list of: id 1) all tables in database TEST of which the schemaname starts with audit id 7) all tables in database TEST in the schema
How to select a specific number of rows and convert them into named columns
I am hanging on this problem for hours. A very simple query: SQL Result: RoomId WaterMeterNumber 95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF SZ12800491 95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF 3375791 95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF 45332 95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF SK9649 And I want the query result to be like that: RoomId Number1 Number2 Number3 Number4 Number5 95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF SZ12800491 3375791 45332 SK9649 I always want to show the numbers of the first five watermeters of a room.
Build a report with SQL with combining columns and rows?
Here is my table: Table Purchase Report PURC_ID CUS_ID Product QTY Date 1 John Leg_01 2 2021-04-09 2 John Head_01 1 2021-04-09 3 Dawn Head_01 1 2021-04-09 4 Dawn Shoulder_01 2 2021-04-09 5 Dawn Leg_01 1 2021-04-09 6 Keith Leg_01 2 2021-04-09 I would like to build the report as follow: Table 4: (PURC table will combine with other columns.
Get rows of Last hour, Last ‘x’ minutes in SQL Server
Order ID Picker ID Time_Picked 1 111 2020-01-13 11:28:00 2 222 2020-01-13 11:40:00 3 333 2020-01-13 10:00:00 4 444 2020-01-13 9:00:00 5 555 2020-01-13 9:45:00 SELECT * FROM mytable WHERE …