如何迭代不同长度的字符串，用它们的全文替换不同的缩写.用分号分隔的所有缩写

Question

我的问题是；我在一个表中有一个字段，其中包含这样的值：

NP
NP;MC;PE
MC;AB;AT;MI;TC;WM
OS
OG

我想把这些缩写转换成它们的全名。即NP变成核电，OG变成石油和天然气，MI变成军事等

我想要的输出是：

Nuclear Power
Nuclear Power;Military;Pesticides

等等

我把它作为一个函数来创建。我只为一个缩写工作，然后为两个缩写工作。然而，我的问题是，我可能有5个缩写或7个。我知道我目前的方法很糟糕，但不知道如何以正确的方式循环使用。

请注意：我缩短了StackOverflow的缩写列表，但总共有25个。

请进一步注意：我做了自下而上的函数(我不知道为什么(，并使两个值和一个值工作。我已经删除了我为值超过3所做的任何事情，因为我所做的都不起作用。

ALTER FUNCTION [dbo].[get_str_full] 
(
-- Add the parameters for the function here
@str_input VARCHAR(250)
)
RETURNS VARCHAR(250)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result VARCHAR(250)
DECLARE @TEMPSTRING VARCHAR(250) 
DECLARE @TEMPSTRING_RIGHT AS VARCHAR(250)
--  DECLARE @PI_COUNT BIGINT
DECLARE @COUNTER INT
DECLARE @TOTAL_VALS BIGINT
DECLARE @STRING_ST VARCHAR(250)
DECLARE @POS_STR BIGINT
DECLARE @REMAINING_STR VARCHAR(250)
-- Used for easy loop skips
DECLARE @LEFTSKIP AS BIGINT             
SET @LEFTSKIP = 1

SET @Result = @str_input
SET @STRING_ST = @Result
SET @COUNTER = (LEN(@Result) - LEN(REPLACE(@Result,';',''))) + 1
SET @TOTAL_VALS = (LEN(@Result) - LEN(REPLACE(@Result,';',''))) + 1
-- If the string has a semicolon then there's more than one PI value
IF CHARINDEX(';', @Result) > 0
BEGIN       
WHILE @COUNTER > 0
BEGIN
IF @TOTAL_VALS >= 3 -- If counter is more than 2 then there's three or more 
BEGIN
DECLARE @TEMP_VAL BIGINT
SET @TEMP_VAL = 5
END             
ELSE IF @TOTAL_VALS = 2-- Theres 2 
BEGIN
-- Do left two chars first
IF @LEFTSKIP = 1
BEGIN
SET @TEMPSTRING = LEFT(@Result, 2)                                                              
SELECT @TEMPSTRING = CASE @TEMPSTRING
WHEN 'MC' THEN 'Military Contracting'
WHEN 'NP' THEN 'Nuclear'
WHEN 'OG' THEN 'Oil & Gas'
WHEN 'OS' THEN 'Oil Sands'
WHEN 'PM' THEN 'Palm Oil'
WHEN 'PE' THEN 'Pesticides'
ELSE @TEMPSTRING
END
SET @LEFTSKIP = 2
END
ELSE IF @LEFTSKIP = 2       
BEGIN                               
SET @TEMPSTRING_RIGHT = RIGHT(@Result, 2)                                                               
SELECT @TEMPSTRING_RIGHT = CASE @TEMPSTRING_RIGHT
WHEN 'MC' THEN 'Military Contracting'
WHEN 'NP' THEN 'Nuclear'
WHEN 'OG' THEN 'Oil & Gas'
WHEN 'OS' THEN 'Oil Sands'
WHEN 'PM' THEN 'Palm Oil'
WHEN 'PE' THEN 'Pesticides'
ELSE @TEMPSTRING_RIGHT
END
END
END         
SET @COUNTER = @COUNTER - 1
END
SET @Result = CONCAT(@TEMPSTRING,';', @TEMPSTRING_RIGHT)
END
ELSE
BEGIN
SET @Result = REPLACE(@Result, 'MC', 'Military Contracting')
SET @Result = REPLACE(@RESULT, 'NP', 'Nuclear Power')
SET @Result = REPLACE(@Result, 'OG', 'Oil & Gas')
SET @Result = REPLACE(@Result, 'OS', 'Oil Sands')
SET @Result = REPLACE(@Result, 'PM', 'Palm Oil')
SET @Result = REPLACE(@Result, 'PE', 'Pesticides')
END

-- Return the result of the function
RETURN @Result
END

Answer 1

首先是一些易于消耗的样本数据：

DECLARE @tranlation TABLE(tCode VARCHAR(10), tString VARCHAR(40));
DECLARE @t TABLE(String VARCHAR(1000));
INSERT  @t VALUES('PE;N'),('NP'),('NP;MC;PE;XX')
INSERT  @tranlation VALUES ('N','Nukes'),('NP','Nuclear Power'),('MC','Military'),
('PE','Pesticides');

注意我更新的样本数据，它包括"；XX"；，没有匹配项；N〃；对于"；Nukes"；这将破坏任何利用REPLACE的解决方案。如果使用SQL 2016+，则可以使用STRING_SPLIT和STRING_AGG。

SELECT
OldString = t.String,
NewString = STRING_AGG(ISNULL(tx.tString,items.[value]),';')
FROM        @t                         AS t
OUTER APPLY STRING_SPLIT(t.String,';') AS items
LEFT JOIN   @tranlation                AS tx
ON        items.[value] = tx.tCode
GROUP BY    t.String ;

退货：

OldString         NewString
----------------- -------------------------------------------
NP                Nuclear Power
NP;MC;PE;XX       Nuclear Power;Military;Pesticides;XX
PE;N              Pesticides;Nukes

Answer 2

您应该真正修复您的表设计，这样就不会在一列中存储多条信息。

如果你想把它作为一个函数，我强烈建议你使用内联表值函数，而不是标量函数

如果您的SQL Server版本为2017+，则可以使用STRING_SPLIT和STRING_AGG。

CREATE OR ALTER FUNCTION GetFullStr
( @str varchar(250) )
RETURNS TABLE
AS RETURN
(
SELECT STRING_AGG(ISNULL(v.FullStr, s.value), ';') result
FROM STRING_SPLIT(@str, ';') s
LEFT JOIN (VALUES
('MC', 'Military Contracting'),
('NP', 'Nuclear'),
('OG', 'Oil & Gas'),
('OS', 'Oil Sands'),
('PM', 'Palm Oil'),
('PE', 'Pesticides')
) v(Abbr, FullStr) ON v.Abbr = s.value
);
GO

您可以也应该用实际的表替换VALUES。

在2016年，您将需要FOR XML PATH而不是STRING_AGG:

CREATE OR ALTER FUNCTION GetFullStr
( @str varchar(250) )
RETURNS TABLE
AS RETURN
(
SELECT STUFF(
(SELECT ';' + ISNULL(v.FullStr, s.value)
FROM STRING_SPLIT(@str, ';') s
LEFT JOIN (VALUES
('MC', 'Military Contracting'),
('NP', 'Nuclear'),
('OG', 'Oil & Gas'),
('OS', 'Oil Sands'),
('PM', 'Palm Oil'),
('PE', 'Pesticides')
) v(Abbr, FullStr) ON v.Abbr = s.value
FOR XML PATH(''), TYPE
).value('text()[1]','varchar(2500)'),
, 1, 1, '')
);
GO

你这样使用它：

SELECT s.result AS FullStr
FROM table
OUTER APPLY GetFullStr(value) AS s;
-- alternatively
SELECT (SELECT * FROM GetFullStr(value)) AS FullStr
FROM table;

Answer 3

您可以将缩写映射分配给TABLE变量，然后将其用于REPLACE。您可以将其构建为一个函数，然后将字符串值传入。

下面的测试返回Military:Nuclear Power:XX。

declare @mapping table (abbrev varchar(50), fullname varchar(100))
insert into @mapping(abbrev, fullname)
values ('NP','Nuclear Power'),
('MC','Military')
declare @testString varchar(100), @newString varchar(100)
set @teststring = 'MC:NP:XX'
set @newString = @testString
SELECT @newString = REPLACE(@newString, abbrev, fullname) FROM @mapping
select @newString