I am sorry as I couldn't reply earlier. I figured DQS trims the data automatically and output trimmed column, so every column you want to be trimmed you need to pass it through DQS.
For replacing a special character in a column we can use MDS user scripts in business rules. We can make our custom SP and call it in MDS UI just as any massaging SP's.
CREATE PROCEDURE [usr].[SetCodeNameDerivedValue]
(@MemberIdList mdm.[MemberId] READONLY -- memberID is a user defined table data type.
-- This parameter will pass along a list of members for which the action needs to run.
,@ModelName NVARCHAR(MAX)
,@VersionName NVARCHAR(MAX)
,@EntityName NVARCHAR(MAX)
,@BusinessRuleName NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @Code NVARCHAR(MAX) = '0';
WHILE(1 = 1)
BEGIN
SELECT @Code = MIN(Code)
FROM @MemberIdList WHERE Code > @Code
IF @Code IS NULL
Begin
Break;
End
Else
Begin
INSERT INTO [stg].[Entity_Leaf]
(ImportType
,BatchTag
,Code
,CodeNameDerivedColumn)
SELECT
0 -- import type 0 = inserting or updating members
,N'Business Rule Extension Test'
,Code -- code of the member, supplied by @MemberIdList
,'A'+Code+LEFT(REPLACE(Name, ',',''),5)
FROM mdm.EntityView where code = @Code;
End
END
-- run the staging batch job to process the staging records
EXEC [stg].[udp_Entity_Leaf]
@VersionName = @VersionName
,@BatchTag = N'Business Rule Extension Test';
END
Here is the link to make custom/extended business rules in MDS
Business Rule Extension in Master data services
The problem is that the list MDS pass to this SP only contains code values of subjected records and no other attribute is passed in @MemberIDList variable, So, I have to bring other attributes from MDS entity subscription views.
Also, I needed to use a while loop, which is working as foreach loop in this scenerio and would run only for those records which are passed in this SP. I could have not used the loop, but as we know that, MDS sometimes apply business rules in batches and sometime only for a single record updated, we don't want to run cleansing on whole table, every time the record is changed.
I think, this feature of MDS is amazing, as we can achieve almost anything, what we would be doing in massaging SP's, though it has certain limitations as well.
