SQL Remove the string between two characters

I have a string such as this: `a|b^c|d|e^f|g` and I want to maintain the pipe delimiting, but remove the carrot sub-delimiting, only retaining the first value of that sub-delimiter. The output result would be: `a|b|d|e|g` Is there a way I can do this

Short int format on time in SQL Server

I have a time column showing up as an int. 800 would be 08:00am and 1300 would be 1:00pm. How can I format the int so that it appears as 12h time, e.g. 1:13 pm? I'm using SQL Server. ThanksYou can do something like below to achieve the required resul

to generate 10000 consecutive integers

Is there a better way to generate [0 ... 9999] than this: SELECT (a3.id + a2.id + a1.id + a0.id) id FROM ( SELECT 0 id UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT

Insert SQL for multiple records

I get the error message when I run the following query in MSSQL Server 2005. Error Message is Incorrect syntax near ','. I think query is ok. But I don't know why I get error. INSERT INTO PERSON (ID, EMP_NAME) VALUES ('E001', 'AAA'), ('E002', 'BBB');

Add data from the table to another SQL SERVER table

please i need your help i have two tables the first one is full with all calendar data dim_table_start(pk_date_deb,year,month,trimester,week,date) and i want to fill the second table with data from dim_data_start dim_table_end(pk_date_fin,year,month,

What is the most accurate way to use DATEDIFF in SQL Server?

I have two computed columns (MonthsInService and YearsInService) with the following expressions. MonthsInService = (datediff(month,[DateEngaged],getdate())) YearsInService = (datediff(month,[DateEngaged],getdate())/(12)) Now if for example DateEngage

Select multiple values ​​in where the time clause is equal

My table looks like this: Table 1: Note: This table is very large in reality, with lots more columns (20ish) and rows (in the millions) | Time | tmp | productionID | | 10:00:00 | 2.2 | 5 | | 10:00:05 | 5.2 | 5 | | 10:00:11 | 7.4 | 5 | | ...... | 3.2

SQL Server 2005: Numeric Expressions Scale When Adding SUM ()

I have a table called "Jrl" with three columns: code AS varchar(15) total AS numeric(13,2) rem AS numeric(13,4) Let us assume for the sake of the argument, that there is just one row in the table with values '001', 400.00 and 52.1745. Consider t

Quick way to write the SQL server table into the file

I have a remote SQL server. I want to make a local copy of all tables in this server. I don't care about the file format used locally, I'm looking for the fastest approach of getting the data from SQL server into the file. (note: server side backup i

update a column based on the maximum date and the group by

i have three tables orders, orders_delivered, orders_delivered_sta and the data in the three tables look like table orders orders_id 10 11 12 13 table orders_delivered orders_delivered_id orders_id 10 1000 10 1001 11 1002 12 1003 12 1004 13 1005 13 1

Entity Framework - Excessive Data Loads with Views

We are having a problem navigating between entities one of which is based on a view. The problem is when we go TableEntity.ViewEntity.Where(x => x.Id == Id).FirstOrDefault()) In the background it is loading all records in the view which is not what w

Get SQL Agent Service Service Account Details

How can I get the Service Account name for the SQL Agent service for a particular SQL Server (SQL 2005). Is it possible to get using SQL statements or WMI ?You can use the sc.exe. To find SQL instances services: sc \\<remote computer name> query | f

What is the use of the 'using' block in c #?

Possible Duplicate: What is the C# Using block and why should I use it? Whats the significance of using block? Why should i write my code inside using block? eg: SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["newConnect

Alternative for the union in SQL where-clause

in where clause i am using like SELECT name FROM products WHERE id IN ( SELECT product_id FROM orders UNION SELECT 1); it taking time to perform union. please provide some other alternative to improve my query performancewhat about select name from p

SQL queries in the Case statement

I have a table named tblAccount where I have two columns: DateCreated AccountApprovalDate DateCreated is the date on which the account is created and AccountApprovalDate is the date on which the account is approved. I need to create a column named 'A

Simple SQL query datetime count queries drive me crazy!

Thank you to anyone that can help. I am using MSSQL 2005 I have a database like so SegmentDT DateTime, WhoCalled varchar(4) All I want to do is find all of the WhoCalled that have a count of calls within 5 minutes of each other. How can I accomplish

No line in MSDB.dbo.sysmail_faileditems

Initially I was getting a "no SELECT privilege" error when attempting to query contents for a report I need to create. So we granted DatabaseMailUserRole in MSDB to my account - I see columns, but none of the data we know to exist. What am I mis

Serialization list in CLR C # aggregation function

I'm trying to add an aggregation function into SQL server 2005 via C#. But when I deploy the project it fails with the following error: .Net SqlClient Data Provider: Msg 6222, Level 16, State 1, Line 1 Type "AggregationTest.CountNonintersectingInterv

Visual Studio 2008 with SQL Server 2005 Developer Edition

I am trying to add a database to the App_Data location in an ASP.NET MVC 2 application in Visual Studio 2008 (VS). I have SQL Server 2005 Developer Edition installed on the local machine. However when adding the database VS complains that SQL Server

Some SQL questions

I have been using SQL for years, but have mostly been using the query designer within SQL Studio (etc.) to put together my queries. I've recently found some time to actually "learn" what everything is doing and have set myself the following fair

Line Column Transposition in SQL Server 2005/2000

In SQL Server 2005, I have a table say tbl_Info. There I have one column say Info. The values are as under Name1 Age1 Sex1 Name2 Age2 Sex2 ..... ..... ..... And the row values goes on like this. My question is how to make row col transposition. The d

The fastest SQL 2005 selection query for ASP .NET table paging?

What is the fastest way to select a range of rows, let's say from 4.200.000 to 4.200.050, using SQL 2005? Suppose that I have 10 millions of rows. On my own projects I use the following approach, but I'm not sure if this is the best practice select *

Dimension Security in SSAS & amp; SSRS

I am stuck with a problem of implementing security at dimension level in SSAS. Here is what I did - 1. Defined a role in SSAS and applied security at dimension level (Unchecking cube dimensions that I don't want this role to access and setting Allowe

Long query prevents inserts

I have a query that runs each night on a table with a bunch of records (200,000+). This application simply iterates over the results (using a DbDataReader in a C# app if that's relevant) and processes each one. The processing is done outside of the d

Determine the time zone offset in T-SQL

My database application is going to be deployed at multiple sites in different time zones. I need a T-SQL function that will determine the UTC timestamp of midnight on January 1 of the current year for YTD calculations. All of the data is stored in U