如何在sql server中创建函数以
按空格分隔字符串定界符并对其重新排序,而myinput为mycolumn名称时,
名称为reFormat(myinput)注意:应删除“ []”,并且应将“-”连接到新的输出而不是空格。新订单(将第三组移动到开头)示例:myinput:[1A] 1B 1C 1D输出:1C_1A_1B_1D
从mytable中选择myinput
[1A] 1B 1C 1D
从mytable
1C_1A_1B_1D中选择reFormat(myinput)
您是否需要以下内容:
CREATE FUNCTION [dbo].[reFormat]
(
@inText varchar(max)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @outText VARCHAR(255);
DECLARE @node VARCHAR(100)
DECLARE @node3 VARCHAR(100)
DECLARE @pos INT
DECLARE @count INT;
SET @count = 1;
DECLARE @space CHAR(1) = '_'
WHILE CHARINDEX(' ', @inText) > 0
BEGIN
SELECT @pos = CHARINDEX(' ', @inText)
SELECT @node = REPLACE(REPLACE(SUBSTRING(@inText, 1, @pos-1), '[',''), ']','');
IF @count = 3
BEGIN
SET @node3 = @node;
END
ELSE
BEGIN
SET @outText = CONCAT(@outText, @space + @node);
END
SET @count = @count + 1;
SELECT @inText = SUBSTRING(@inText, @pos+1, LEN(@inText)-@pos)
END
SELECT @node = REPLACE(REPLACE(@inText, '[',''), ']','');
SET @outText = CONCAT(@node3, @outText) + @space + @node;
RETURN @outText;
END
以及使用它的代码:
CREATE TABLE #MyTable
(myinput varchar(255));
INSERT INTO #MyTable ( myinput ) VALUES ( '[1A] 1B 1C 1D');
select dbo.reFormat(myinput) from #MyTable
DROP TABLE #MyTable;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句