CODEWITHSUNDEEP
sqlsql-serverfunctionsql-server-2014
Chan Yoong Hon
Chan Yoong Hon

Reputation: 1812

SQL Function: Modify String and Return in MSSQL Server 2014

I am new in SQL function, I have string @string = "233, 344, 555". How can I modify of my string and return it as "a_233, b_344, c_555"?

In my function, I have to split of the string with "," character. In the first number, I have to add a_firstnumber, in the second number I have to add c_secondnumber and continue with third number and so on.

Next, in my function I have to return it as string also. like "a_233, b_344, c_555".

How can I do it in MSSQL Function?

Upvotes: 2

Views: 819

Answers (4)

cloudsafe
cloudsafe

Reputation: 2506

Changing to rows and back using XML:

alter function dbo.ModifyString(@input Varchar(max), @Splitter VarChar(99)) returns nvarchar(max)
as
begin
declare @str as nvarchar(max)
;with cte as ( SELECT  ltrim(Split.a.value('.', 'VARCHAR(max)')) AS Data 
    FROM
    ( SELECT CAST ('<M>' + REPLACE(@input, @Splitter, '</M><M>') + '</M>' AS XML) AS Data 
    ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a))
 select @str = stuff((
        select ', ' + char(row_number() over (order by [data]) + 96) + '_' + [Data]
        from cte x
        order by x.Data
        for xml path('')
    ),1,2,'') 
from cte x;
return @str
end

declare @string as nvarchar(max) = '233, 344, 555'

select dbo.ModifyString(@string, ',' )

Upvotes: 0

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

An another easy way of doing is

DECLARE @string VARCHAR(MAX)= '233, 344, 555', @STRING2 VARCHAR(MAX)='';

SELECT @STRING = '<M>'+REPLACE(@STRING,', ','</M><M>')+'</M>';

SELECT @STRING2 = @STRING2+',' +NEW FROM (
SELECT char( row_number() over(order by(select 1))+64)+'_'+
SPLT.X.value('.','varchar(10)')  AS NEW
FROM(
SELECT CAST(@STRING AS XML) SXML
)A 
CROSS APPLY 
  SXML.nodes('/M') as SPLT(X)
)BB

SELECT SUBSTRING(@STRING2,2,LEN(@STRING2))

Result:

 A_233,B_344,C_555

Upvotes: 2

7schmiede
7schmiede

Reputation: 104

DECLARE @string NVARCHAR(MAX) = '233, 344, 555';

DECLARE @tParts TABLE (
    id INT IDENTITY (1, 1),
    part NVARCHAR(10)
);

-- Split your String with a Split-Function like

--INSERT INTO @tParts
--SELECT item 
--FROM dbo.Split(@string, ',');

-- Or use Demo-Values
INSERT INTO @tParts 
VALUES (N'1'), (N'2'), (N'3') -- ...

DECLARE @tPrefixes TABLE (
    id INT IDENTITY (1, 1),
    prefix NVARCHAR(10)
);

INSERT INTO @tPrefixes 
VALUES (N'a'), (N'b'), (N'c') -- ...

DECLARE @output NVARCHAR(MAX) = N'';

SELECT @output += pr.prefix + N'_' + ps.part + N', '
FROM @tPrefixes AS pr
    INNER JOIN @tParts AS ps
        ON ps.id = pr.id;

-- Remove last N', '
SET @output = LEFT(@output, LEN(@output)-1);

SELECT @output AS [output];

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use value constructure and do the JOIN :

select concat(t.chars, '_', t1.function_out) as numbers
from ( values (1, 'a'), (2, 'b'), (3, 'c') . . . ) t(id, chars) inner join 
           (select row_number() over (order by (select 1)) as id, 
                   function_out 
            from <your split function here>(@string, ',')
           ) t1 on t1.id = t.id;

Upvotes: 0

Related Questions