Replace function in TEXT or NTEXT fields SQL Server We often use replace function in SQL server databases to find and replace a specific string. The SQL statement is straightforward and there is no any issue when we search and replace a string on a column with a datatype, not either TEXT or NTEXT. The general search and replace SQL statement is UPDATE tbl_name SET column_name = REPLACE(column_name, 'old_string', 'new_string') WHERE column_name like '%old_string%' The problem occurs when you try to search and replace a string on a column with datatype TEXT or NTEXT and the error message: Argument data type text is invalid for argument 1 of replace function. and SQL server do not have any recommendation on how to resolve, probably we know REPLACE function only work on varchar or nvarchar datatype The simplest way is converted TEXT or NTEXT data to NVARCHAR(MAX) and then use Replace function. Using below query you can replace string in TEXT or NTEXT data type column. UPDATE tbl_name SET column_name = CAST(REPLACE(CAST(column_name as nvarchar(MAX)),'old_string','new_string') AS NText) WHERE column_name LIKE '%old_string% This way you can replace strings in TEXT or NTEXT column.

Securing values for other tables from enumerable table in SQL Server database

AnotherOps

English is not my native language, so I might have misused words Enumerator and Enumerable in this context. Please get a feel for what I'm trying to say and correct me if I'm wrong.

I'm looking into not having tables for each enumerator I need in my database.

I "don't want" to add tables for (examples:) service duration type, user type, currency type, etc. and add relations for each of them.

Instead of a table for each of them which values will probably not change a lot, and for which I'd have to create relationships with other tables, I'm looking into having just 2 tables called Enumerator (eg: user type, currency...) and Enumerable (eg: for user type -> manager, ceo, delivery guy... and for currency -> euro, dollar, pound...).

Though here's the kicker. If I implement it like that, I'm loosing the rigidity of the foreign key relationships -> I can't accidentally insert a row in users table that will have a user type of some currency or service duration type, or something else.

Is there another way to resolve the issue of having so many enumerators and enumerables with the benefit of having that rigidity of the foreign key and with the benefit of having all of them in just those 2 tables?

Best I can think of is to create a trigger for BEFORE UPDATE and BEFORE INSERT to check if (for example) the column type of user table is using the id of the enumerable table that belongs to the correct enumerator.

This is a short example in SQL

CREATE TABLE [dbo].[Enumerator]
(
    [Id] INT NOT NULL PRIMARY KEY,
    [Name] VARCHAR(50)
)

CREATE TABLE [dbo].[Enumerable]
(
    [Id] INT NOT NULL PRIMARY KEY,
    [EnumeratorId] INT NOT NULL FOREIGN KEY REFERENCES Enumerator(Id),
    [Name] VARCHAR(50)
)

INSERT INTO Enumerator (Id, Name) 
VALUES (1, 'UserType'),
       (2, 'ServiceType');

INSERT INTO Enumerable (Id, EnumeratorId, Name)   -- UserType
VALUES (1, 1, 'CEO'),
       (2, 1, 'Manager'),
       (3, 1, 'DeliveryGuy');

INSERT INTO Enumerable (Id, EnumeratorId, Name)   -- ServiceDurationType
VALUES (4, 2, 'Daily'),
       (5, 2, 'Weekly'),
       (6, 2, 'Monthly');

CREATE TABLE [dbo].[User]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY (1,1),
    [Type] INT NOT NULL FOREIGN KEY REFERENCES Enumerable(Id)
)

CREATE TABLE [dbo].[Service]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY (1,1),
    [Type] INT NOT NULL FOREIGN KEY REFERENCES Enumerable(Id)
)

The questions are:

Is it viable to resolve enumerators and enumerables with 2 tables and with before update and before insert triggers, or is it more trouble than it's worth?

Is there a better way to resolve this other than using before update and before insert triggers?

Is there a better way to resolve enumerators and enumerables that is not using 2 tables and triggers, nor creating a table with relations for each of them?

I ask for your wisdom as I don't have one or more big projects behind me and I didn't get a chance to create a DB like this until now.