Māori language support SQL columns

  • Hi , We have all Varchar type columns in a table.  We have a new requirement which needs to have both varchar columns as well as new column with the collation to support Māori  language. So, I decided to create a new Nvarchar column . But now the problem is user will enter the data from the front end for one of the column with Māori  character, from trigger, I need to update other column without the Māori  characters.

    What I tried is,

    CREATE TABLE [dbo].[Collationtest](

    [Title] [nvarchar](60) NULL,

    [TitleNormal] [nvarchar](60) NULL,

    [ID] [int] IDENTITY(1,1) NOT NULL

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    Declare @var Nvarchar(60)

    set @Var = N'kaka means clothes, kakā means spicy, and kākā means parrot.'

    INSERT INTO [dbo].[Collationtest]([Title]) values(@Var)

    Trigger

    ALTER TRIGGER [dbo].[trg_Collationtest]

    ON [dbo].[Collationtest]

    AFTER INSERT,UPDATE

    AS

    BEGIN

    DECLARE @ID INT

    DECLARE @Title NVarchar(60)

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for trigger here

    SELECT @ID = ID, @TitleDesc = Title  FROM inserted

    Update Collationtest SET TitleNormal = Substring(@Title,1,LEN(@Title)) WHERE ID = @ID

    END

    But, both columns in the backend showing as below.

    kaka means clothes, kakā means spicy, and kākā means parrot.

    How can I achieve the update of the TitleNormal column without unicode characters when I insert  data with unicode characters in Title column?

    Any help would be appreciated.

     

  • Your trigger will fail if more than one row is inserted or updated in a batch, so I suggest that you fix that.

    What do you want TitleNormal to be updated to in the case of your example? You say 'without unicode characters', which possibly implies this

    kaka means clothes, kak means spicy, and kk means parrot.

    Is that your requirement?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • I worked the above with Cast to varchar when updating the TitleNormal.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply