我有一个表,其中有2列:optionValues
和optionNames
。optionNames
包含组合框的文本和optionValues
每个文本的值,两者均用分隔~
。例如 :
optionValues | optionNames
0~1 | male~female
我正在尝试创建一个函数,该函数将optionValue作为参数并返回optionName。使用上面的示例:
fn.GetOptionName(1) --will return `female`
如何分隔值并取回正确的值?
这是我开始的工作,但由于不分隔值而无法使用:
select @on = OptionNames from dbo.Table
where tablename = @tablename and fieldname = @fieldname and optionvalues = @fieldvalue
(无论数据库模式是好是坏,我都无法更改它)
编辑
找到此函数,该函数在特定位置返回子字符串:DelimitedSplit8K
请尝试这个(SQL 2016 +)
资料产生
CREATE TABLE Splits
(
optionValues varchar(20)
,optionNames varchar(200)
)
GO
INSERT INTO Splits VALUES
('0~1','male~female'),
('0~1~2','male~female~Trans'),
('0~1','male~F')
GO
解
DECLARE @Find AS VARCHAR(10) = '1'
;WITH CTE0 AS
(
SELECT * , ROW_NUMBER() OVER (ORDER BY ( SELECT NULL ) ) rnk FROM Splits
)
,CTE AS
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY rnk ORDER BY rnk) rnk1 FROM CTE0
CROSS APPLY
(
SELECT Value
FROM STRING_SPLIT(optionValues, '~'))
p
)
,CTE1 AS
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY rnk ORDER BY rnk) rnk1 FROM CTE0
CROSS APPLY
(
SELECT Value
FROM STRING_SPLIT(optionNames, '~'))
p1
)
SELECT s.*,ISNULL(p.value,'') Value FROM Splits s
LEFT JOIN
(
SELECT a.optionValues, a.optionNames , b.value FROM CTE a
INNER JOIN CTE1 b ON a.optionNames = b.optionNames
AND a.optionValues = b.optionValues AND a.rnk = b.rnk AND a.rnk1 = b.rnk1
WHERE a.value = @Find
)p ON p.optionValues = s.optionValues AND p.optionNames = s.optionNames
输出值
optionValues optionNames Value
-------------------- ------------------------ ---------
0~1 male~female female
0~1~2 male~female~Trans female
0~1 male~F F
(3 rows affected)
为SQL 2012+解决方案添加
DECLARE @Find AS VARCHAR(10) = '1'
;WITH CTE0 AS
(
SELECT * , ROW_NUMBER() OVER (ORDER BY ( SELECT NULL ) ) rnk FROM Splits
)
,CTE AS
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY rnk ORDER BY rnk) rnk1 FROM
(
SELECT *,CAST('<A>'+ REPLACE(optionValues,'~','</A><A>')+ '</A>' AS XML) po
FROM CTE0
)rt
CROSS APPLY ( SELECT t.value('.', 'VARCHAR(10)') Value FROM po.nodes('/A') AS x(t) ) o
)
,CTE1 AS
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY rnk ORDER BY rnk) rnk1 FROM
(
SELECT *,CAST('<A>'+ REPLACE(optionNames,'~','</A><A>')+ '</A>' AS XML) po
FROM CTE0
)rt
CROSS APPLY ( SELECT t.value('.', 'VARCHAR(10)') Value FROM po.nodes('/A') AS x(t) ) o
)
SELECT s.*,ISNULL(p.value,'') Value FROM Splits s
LEFT JOIN
(
SELECT a.optionValues, a.optionNames , b.value , a.rnk FROM CTE a
INNER JOIN CTE1 b ON a.optionNames = b.optionNames
AND a.optionValues = b.optionValues AND a.rnk = b.rnk AND a.rnk1 = b.rnk1
WHERE a.value = @Find
)p ON p.optionValues = s.optionValues AND p.optionNames = s.optionNames
ORDER BY rnk
输出值
optionValues optionNames Value
-------------------- ----------------------- ----------
0~1 male~female female
0~1~2 male~female~Trans female
0~1 male~F F
(3 rows affected)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句