**IF** your data won&#39;t overflow 4000 characters **AND** you&#39;re on SQL Server 2000 or compatibility level of 8 or SQL Server 2000:

    UPDATE [CMS_DB_test].[dbo].[cms_HtmlText] 
    SET Content = CAST(REPLACE(CAST(Content as NVarchar(4000)),&#39;ABC&#39;,&#39;DEF&#39;) AS NText)
    WHERE Content LIKE &#39;%ABC%&#39; 

For SQL Server 2005+:

    UPDATE [CMS_DB_test].[dbo].[cms_HtmlText] 
    SET Content = CAST(REPLACE(CAST(Content as NVarchar(MAX)),&#39;ABC&#39;,&#39;DEF&#39;) AS NText)
    WHERE Content LIKE &#39;%ABC%&#39; 


Assuming SQL Server 2000, the following [StackOverflow question][1] should address your problem.

If using SQL Server 2005/2008, you can use the following code (taken from [here][2]):

    select cast(replace(cast(myntext as nvarchar(max)),&#39;find&#39;,&#39;replace&#39;) as ntext)
    from myntexttable


  [1]: https://stackoverflow.com/questions/2133635/replace-ntext-more-than-4000-characters-in-sql-server-2000
  [2]: http://www.sidesofmarch.com/index.php/archive/2008/02/27/how-to-use-replace-within-ntext-columns-in-sql-server/

I have some difficulties with mySQL commands that I want to do. 

    SELECT a.timestamp, name, count(b.name) 
    FROM time a, id b 
    WHERE a.user = b.user
      AND a.id = b.id
      AND b.name = &#39;John&#39;
      AND a.timestamp BETWEEN &#39;2010-11-16 10:30:00&#39; AND &#39;2010-11-16 11:00:00&#39; 
    GROUP BY a.timestamp

This is my current output statement.


    timestamp            name  count(b.name)
    -------------------  ----  -------------
    2010-11-16 10:32:22  John  2
    2010-11-16 10:35:12  John  7
    2010-11-16 10:36:34  John  1
    2010-11-16 10:37:45  John  2
    2010-11-16 10:48:26  John  8
    2010-11-16 10:55:00  John  9
    2010-11-16 10:58:08  John  2

How do I group them into 5 minutes interval results?

I want my output to be like

    timestamp            name  count(b.name)
    -------------------  ----  -------------
    2010-11-16 10:30:00  John  2
    2010-11-16 10:35:00  John  10
    2010-11-16 10:40:00  John  0
    2010-11-16 10:45:00  John  8
    2010-11-16 10:50:00  John  0
    2010-11-16 10:55:00  John  11 


Grouping into interval of 5 minutes within a time range

For example, in one place...

    //---------------a
    try
    {
        // some network call
    }
    catch(WebException we)
    {
        throw new MyCustomException(&quot;some message ....&quot;, we);
    }

...and in another place...
    
    //--------------b
    try
    {
        // invoke code above
    }
    catch(MyCustomException we)
    {
        Debug.Writeline(we.stacktrace);   // &lt;----------------
    }

The stacktrace I print, it only start from a to b,
it doesnt include the inner stacktrace from the WebException.

How can I print all the stacktrace???

How to print full stack trace in exception?

I need to update/replace the data in datatable.column. The table has a field named `Content`. I&#39;m using the `REPLACE` function. Since the column datatype is `NTEXT`, SQL Server doesn&#39;t allow me to use the `REPLACE` function.

I can&#39;t change the datatype because this database is 3rd party software table. Changing the datatype will cause the application to fail.

    UPDATE [CMS_DB_test].[dbo].[cms_HtmlText] 
    SET Content = REPLACE(Content,&#39;ABC&#39;,&#39;DEF&#39;) 
    WHERE Content LIKE &#39;%ABC%&#39; 

I Receive this error:
&gt;Msg 8116, Level 16, State 1, Line 1
Argument data type ntext is invalid for argument 1 of replace function.


- Can I fix this with T-SQL? Does someone have an example how to read and to loop?
- Since this is onetime conversion, maybe I can change to another type but I&#39;m afraid I&#39;m messing up the data.

There is a primary key field: name: ID  - integer - it&#39;s an identity.... So I need to think about this too. Maybe set the Identity to N temporary.

Please advise on how to achieve the REPLACE function?

Approx. 3000 statements need to be updated with a new solution.

alternatives to REPLACE on a text or ntext datatype

I need to update/replace the data in datatable.column. The table has a field named <code>Content</code>. I'm using the <code>REPLACE</code> function. Since the column datatype is <code>NTEXT</code>, SQL Server doesn't allow me to use the <code>REPLACE</code> function.
I can't change the datatype because this database is 3rd party software table. Changing the datatype will cause the application to fail.
<pre><code class="hljs language-ini">UPDATE [CMS_DB_test].[dbo].[cms_HtmlText] 
SET Content = REPLACE(Content,'ABC','DEF') 
WHERE Content LIKE '%ABC%' 
</code></pre>
I Receive this error:
>Msg 8116, Level 16, State 1, Line 1
Argument data type ntext is invalid for argument 1 of replace function.
<ul>
<li>Can I fix this with T-SQL? Does someone have an example how to read and to loop?</li>
<li>Since this is onetime conversion, maybe I can change to another type but I'm afraid I'm messing up the data.</li>
</ul>
There is a primary key field: name: ID - integer - it's an identity.... So I need to think about this too. Maybe set the Identity to N temporary.
Please advise on how to achieve the REPLACE function?
Approx. 3000 statements need to be updated with a new solution.

I have a function that updates three tables, but I use three queries to perform this. I wish to use a more convenient approach for good practice.

How can I update multiple tables in MySQL with a single query?

MySQL, update multiple tables with one query

When should a View actually be used over an actual Table? What gains should I expect this to produce?

Overall, what are the advantages of using a view over a table? Shouldn&#39;t I design the table in the way the view should look like in the first place?

When to use a View instead of a Table?

Say I have a table column that has results like:

    ABC_blahblahblah
    DEFGH_moreblahblahblah
    IJKLMNOP_moremoremoremore

I would like to be able to write a query that selects this column from said table, but only returns the substring up to the Underscore (_) character. For example:

    ABC
    DEFGH
    IJKLMNOP

The SUBSTRING function doesn&#39;t seem to be up to the task because it is position-based and the position of the underscore varies.

I thought about the TRIM function (the RTRIM function specifically):

    SELECT RTRIM(&#39;listofchars&#39; FROM somecolumn) 
    FROM sometable

But I&#39;m not sure how I&#39;d get this to work since it only seems to remove a certain list/set of characters and I&#39;m really only after the characters leading up to the Underscore character.

How to Select a substring in Oracle SQL up to a specific character?

What is difference between MySQL `Curdate()` and `Now()`?

MySQL: Curdate() vs Now()

Where [CastleType] is set as data type &quot;text&quot; in SQL Server and the query is:

    SELECT *
    FROM   [Village]
    WHERE  [CastleType] = &#39;foo&#39; 

I get the error:

&gt;The data types **TEXT** and **VARCHAR** are incompatible in the equal to operator.

Can I not query this data type with a WHERE clause?

WHERE clause on SQL Server &quot;Text&quot; data type

I need a SQL statement to delete row that are older than 30 days.

My table `events` has a field `date` that contains the date and the time it was inserted in the database. 

Will this work?  
```SELECT * from Results WHERE [Date] &gt;= DATEADD(d, -30, getdate())```

Delete rows with date older than 30 days with SQL Server query

How do I design the database to calculate the account balance?

1) Currently I calculate the account balance from the transaction table
In my transaction table I have &quot;description&quot; and &quot;amount&quot; etc..

I would then add up all &quot;amount&quot; values and that would work out the user&#39;s account balance.

-----------
I showed this to my friend and he said that is not a good solution, when my database grows its going to slow down???? He said I should create separate table to store the calculated account balance. If did this, I will have to maintain two tables, and its risky, the account balance table could go out of sync.

Any suggestion?


**EDIT**: OPTION 2: should I add an extra column to my transaction tables &quot;Balance&quot;.
now I do not need to go through many rows of data to perform my calculation.

Example
John buys $100 credit, he debt $60, he then adds $200 credit.

Amount $100, Balance $100.

Amount -$60, Balance $40.

Amount $200, Balance $240.

Database design: Calculating the Account Balance

I&#39;m running some administrative queries and compiling results from `sp_spaceused` in SQL Server 2008 to look at data/index space ratios of some tables in my database.  Of course I am getting all sorts of large numbers in the results and my eyes are starting to gloss over.  It would be really convenient if I could format all those numbers with commas (987654321 becomes 987,654,321).  Funny that in all the many years I&#39;ve used SQL Server, this issue has never come up since most of the time I would be doing formatting at the presentation layer, but in this case the T-SQL result in SSMS **is** the presentation.

I&#39;ve considered just creating a simple CLR UDF to solve this, but it seems like this should be do-able in just plain old T-SQL.  So, I&#39;ll pose the question here - how do you do numeric formatting in vanilla T-SQL?

How do I format a number with commas in T-SQL?

Can I create regions in sql server editor (like `#region` and `#endregion` in C#) ?

sql server #region

I need to update the values of a column, with a substring replace being done on the existing values.

Example:

Data contains `abc@domain1`, `pqr@domain2` etc. 

I need to update the values such that `@domain2` is replaced with `@domain1`.

T-SQL string replace in Update

Can you help me with SQL statements to find duplicates on multiple fields?

For example, in pseudo code:

    select count(field1,field2,field3) 
    from table 
    where the combination of field1, field2, field3 occurs multiple times

and from the above statement *if there are multiple occurrences* I would like to select *every record except the first one*.

Select statement to find duplicates on certain fields

I am creating a script that will be run in a MS SQL server. This script will run multiple statements and needs to be transactional, if one of the statement fails the overall execution is stopped and any changes are rolled back.

I am having trouble creating this transactional model when issuing ALTER TABLE statements to add columns to a table and then updating the newly added column. In order to access the newly added column right away, I use a GO command to execute the ALTER TABLE statement, and then call my UPDATE statement. The problem I am facing is that I cannot issue a GO command inside an IF statement. The IF statement is important within my transactional model. This is a sample code of the script I am trying to run. Also notice that issuing a  GO command, will discard the @errorCode variable, and will need to be declared down in the code before being used (This is not in the code below).

    BEGIN TRANSACTION
    
    DECLARE @errorCode INT
    SET @errorCode = @@ERROR
    
    -- **********************************
    -- * Settings
    -- **********************************
    IF @errorCode = 0
    BEGIN
     BEGIN TRY
      ALTER TABLE Color ADD [CodeID] [uniqueidentifier] NOT NULL DEFAULT (&#39;{00000000-0000-0000-0000-000000000000}&#39;)
      GO
     END TRY
     BEGIN CATCH
      SET @errorCode = @@ERROR
     END CATCH
    END
    
    IF @errorCode = 0
    BEGIN
     BEGIN TRY
      UPDATE Color
      SET CodeID= &#39;B6D266DC-B305-4153-A7AB-9109962255FC&#39;
      WHERE [Name] = &#39;Red&#39;
     END TRY
     BEGIN CATCH
      SET @errorCode = @@ERROR
     END CATCH
    END
    
    -- **********************************
    -- * Check @errorCode to issue a COMMIT or a ROLLBACK
    -- **********************************
    IF @errorCode = 0
    BEGIN
     COMMIT
     PRINT &#39;Success&#39;
    END
    ELSE 
    BEGIN
     ROLLBACK
     PRINT &#39;Failure&#39;
    END

So what I would like to know is how to go around this problem, issuing ALTER TABLE statements to add a column and then updating that column, all within a script executing as a transactional unit.

Content Type	Original Author	Original Content on Stackoverflow
Question	ethem	View Question on Stackoverflow
Solution 1 - Sql	p.campbell	View Answer on Stackoverflow
Solution 2 - Sql	LittleBobbyTables - Au Revoir	View Answer on Stackoverflow

alternatives to REPLACE on a text or ntext datatype

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

How to print full stack trace in exception?

Grouping into interval of 5 minutes within a time range

Attributions