Sql functions manipulation

See more:

SQL

I have written two functions exactly doing the same thing on two SQL tables containing some item information. Removing the unwanted characters and adding on the entries of the first table and creating a translation key as item.groups and on the second table item.class to the result set. My aim is to create a single function doing my job.

First table contain:
itemgroups
group_name
fertilizer1
fertilizer2

Second table
item
group_name | name
fertilizer1 | nitrogen
fertilizer1 | phosphorus
fertilizer2 | potassium
fertilizer2 |sodium

<pre><pre>ALTER  FUNCTION [dbo].[new_translation_groups]
(  
   @column_name nvarchar(1000),
   @group_name nvarchar(1000)
) 
Returns nvarchar(1000)  
As
BEGIN
DECLARE @result nvarchar(1000);
DECLARE @result2 nvarchar(1000);
    
    SET @result = @column_name;
	SET @result2 = @group_name;

	SET @result2 = Replace(@result2, N' OR ', N'_');
	SET @result2 = Replace(@result2, N' and ', N'_');
    SET @result2 = Replace(@result2, N' ', N'_');
	SET @result2 = Replace(@result2, N'/', N'_');
	
	SET @result = Replace(@result, N' OR ', N'_');
	SET @result = Replace(@result, N' and ', N'_');
    SET @result = Replace(@result, N' ', N'_');
	SET @result = Replace(@result, N'-', N'_');

	


    
    WHILE @result Like N'%[_][_]%'
    BEGIN
        SET @result = Replace(@result, N'_', N'');
    END;

	 WHILE @result2 Like N'%[_][_]%'
    BEGIN
        SET @result2 = Replace(@result, N'_', N'');
    END;

	

	RETURN N'item.class.'+LOWER(@result2)+ N'_'+ LOWER(@result);
END;

My second function

CREATE OR ALTER FUNCTION dbo.translationkey_v22
(  
   @column_name nvarchar(1000) 
) 
Returns nvarchar(1000)  
As
BEGIN
DECLARE @result nvarchar(1000);
    
    SET @result = @column_name;
    SET @result = Replace(@result, N'and', N'_');
    SET @result = Replace(@result, N' ', N'_');
    
    WHILE @result Like N'%[_][_]%'
    BEGIN
        SET @result = Replace(@result, N'__', N'');
    END;
    
    Return N'item.group.' + LOWER(@result);
END;

What I have tried:

what i have tried


Alter FUNCTION [dbo].[new_translation_groups_newV1]
(
@column_name nvarchar(1000),
@group_name nvarchar(1000)

)
Returns nvarchar(1000)
As
BEGIN
DECLARE @result nvarchar(1000);
DECLARE @result2 nvarchar(1000);
DECLARE @result3 nvarchar(1000);

SET @result = @column_name;
SET @result2 = @group_name;

SET @result2 = Replace(@result2, N' OR ', N'_');
SET @result2 = Replace(@result2, N' and ', N'_');
SET @result2 = Replace(@result2, N' ', N'_');
SET @result2 = Replace(@result2, N'/', N'_');

SET @result = Replace(@result, N' OR ', N'_');
SET @result = Replace(@result, N' and ', N'_');
SET @result = Replace(@result, N' ', N'_');
SET @result = Replace(@result, N'-', N'_');





WHILE @result Like N'%[_][_]%'
BEGIN
SET @result = Replace(@result, N'_', N'');
END;

WHILE @result2 Like N'%[_][_]%'
BEGIN
SET @result2 = Replace(@result, N'_', N'');
END;


IF (@group_name = (SELECT group_name from itemGroups))
SET @result3 = N'item.group.' + LOWER(@result)
ELSE IF (@group_name = (SELECT group_name from item))
SET @result3= N'item.class.'+LOWER(@result2)+ N'_'+ LOWER(@result);

RETURN @result3

END;


But this is giving error as the query is selecting multiple rows