Category Archives: Database

MSSQL: Remove Diacritic Marks From Characters – Vietnamese unicode to non-unicode in database

Sometimes we want to remove accents from vietnamese words to Latin based characters for search or matching routine in database. However, Microsoft SQL Database does not offer normalized function which we can use to remove accents from words such as:

  1. AI ĐI TRÊN DÀM ĐƯỜNG TRƯỜNG => AI DI TREN DAM DUONG TRUONG
  2. áo em chưa màc một lần => ao em chua mac mot lan

There is  workaround by using CLR function, see example code below:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Collections.Generic;
using System.Globalization;

public partial class UserDefinedFunctions
{
  [Microsoft.SqlServer.Server.SqlFunction]
  public static SqlString fn_RemoveDiacritics(String str)
  {
    // Put your code here
    return new SqlString(RemoveDiacritics(str));
  }

Continue reading

INSTEAD OF DELETE

INSTEAD OF DELETE triggers can be defined on a view or table to replace the standard action of the DELETE statement. Usually, the INSTEAD OF DELETE trigger is defined on a view to modify data in one or more base tables.

DELETE statements do not specify modifications to existing data values. DELETE statements specify only the rows that are to be deleted. The inserted table passed to a DELETE trigger is always empty. The deleted table sent to a DELETE trigger contains an image of the rows as they existed before the DELETE statement was issued. In the case of an INSTEAD OF DELETE trigger on a view or table, the format of the deleted table is based on the format of the select list defined for the view.

Sample:

USE [SampleDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_ShippingImport_Delete]
  ON  [dbo].[ShippingImport]
  INSTEAD OF DELETE
AS 
BEGIN
  SET NOCOUNT ON;
  delete from Import where OrderID in (select OrderID from deleted)
END

via: http://msdn.microsoft.com/en-us/library/ms191208.aspx