SQL SERVER 中的 TRANSLATE 函数 [英] TRANSLATE function in SQL SERVER
问题描述
我读到SQL Server 2017下DB2下有一个相当于标准函数TRANSLATE的函数，但是在早期版本下怎么做?
I read that there is a function equivalent to the standard function TRANSLATE under DB2 under SQL Server 2017. But how to do under earlier versions?
函数定义:这里
推荐答案
改编自 @Shnugo 的回答.这更接近你想要的.你只需要确保你有一个 dbo.numbers 表(它们真的很有用).
Adapted from @Shnugo's answer. This is closer to what you want. You just need to make certain you have a dbo.numbers table (they're REALLY useful to have).
http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=627828307504174dcf3f61313ba384a8
CREATE FUNCTION dbo.MultiReplace(@ReplaceTarget NVARCHAR(MAX), @from_chars NVARCHAR(MAX), @to_chars NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
--Quirky Update: One of the rare situations where this is a good idea
SELECT @ReplaceTarget=REPLACE(@ReplaceTarget,SUBSTRING(@from_chars, id+1, 1), SUBSTRING(@to_chars, id+1, 1))
FROM numbers
WHERE id < LEN(@from_chars) AND id < LEN(@to_chars)
ORDER BY id;
RETURN @ReplaceTarget;
END
还有一种稍微过分的方式来满足您的要求 TRANSLATE('abc', 'abc', 'bcd') =>'bcd').
And a slightly over the top way to meet your requirement that TRANSLATE('abc', 'abc', 'bcd') => 'bcd').
CREATE FUNCTION dbo.Translate(@ReplaceTarget NVARCHAR(MAX), @from_chars NVARCHAR(MAX), @to_chars NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE
@steps INT = LEN('_' + @from_chars + '_') - 2
;
WITH
dictionary(id, string_from, string_interim, string_to) AS
(
SELECT
id, string_from, N'<' + string_from + N'>', string_to
FROM
(
SELECT
id,
ROW_NUMBER() OVER (PARTITION BY string_from ORDER BY id) AS occurence,
string_from,
string_to
FROM
numbers
CROSS APPLY
(
SELECT
CAST(SUBSTRING(@from_chars, numbers.id, 1) AS NVARCHAR(5)) AS string_from,
CAST(SUBSTRING(@to_chars, numbers.id, 1) AS NVARCHAR(5)) AS string_to
)
chars
WHERE
numbers.id > 0
AND numbers.id <= @steps
)
sorted_dictionary
WHERE
occurence = 1
)
,
mapping_sequence(id, string_from, string_to) AS
(
SELECT 1, N'<', N'<<>' WHERE @from_chars LIKE N'%<%'
UNION ALL SELECT 2, N'>', N'<>>' WHERE @from_chars LIKE N'%>%'
UNION ALL SELECT 3, N'<<<>>', N'<<>' WHERE @from_chars LIKE N'%<%' AND @from_chars LIKE N'%>%'
UNION ALL SELECT 3 + id, string_from, string_interim FROM dictionary WHERE string_from NOT IN (N'<', N'>')
UNION ALL SELECT 3 + @steps + id, string_interim, string_to FROM dictionary
)
SELECT
@ReplaceTarget = REPLACE(@ReplaceTarget, string_from, string_to)
FROM
mapping_sequence
ORDER BY
id
;
RETURN @ReplaceTarget;
END
http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=9dbe7214ac4b5bb00060686cfaa879c2"一个>
http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=9dbe7214ac4b5bb00060686cfaa879c2
上述可能的小优化(尽可能减少 REPLACE 调用的次数)...
A possible minor optimisation of the above (To reduce the number of REPLACE calls where possible)...
CREATE FUNCTION dbo.Translate(
@ReplaceTarget NVARCHAR(MAX),
@from_chars NVARCHAR(MAX),
@to_chars NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE
@steps INT = LEN('_' + @from_chars + '_') - 2
;
WITH
dictionary AS
(
SELECT
id, string_from, string_to
FROM
(
SELECT
ROW_NUMBER() OVER ( ORDER BY string_from ) AS id,
ROW_NUMBER() OVER (PARTITION BY string_from ORDER BY id) AS occurence,
string_from,
string_to
FROM
numbers
CROSS APPLY
(
SELECT
CAST(SUBSTRING(@from_chars, numbers.id, 1) AS NVARCHAR(5)) AS string_from,
CAST(SUBSTRING(@to_chars, numbers.id, 1) AS NVARCHAR(5)) AS string_to
)
chars
WHERE
numbers.id > 0
AND numbers.id <= @steps
)
sorted_dictionary
WHERE
occurence = 1
),
two_stage AS
(
SELECT
map.*
FROM
dictionary dict
CROSS APPLY
(
SELECT COUNT(*) FROM dictionary WHERE dictionary.id > dict.id AND dictionary.string_from = dict.string_to
)
remap(hits)
CROSS APPLY
(
SELECT id, dict.string_from, dict.string_to WHERE remap.hits = 0 AND dict.string_from NOT IN (N'<', N'>')
UNION ALL
SELECT id, dict.string_from, N'<' + dict.string_from + N'>' WHERE remap.hits > 0 AND dict.string_from NOT IN (N'<', N'>')
UNION ALL
SELECT id + @steps, N'<' + dict.string_from + N'>', dict.string_to WHERE remap.hits > 0 AND dict.string_from NOT IN (N'<', N'>')
UNION ALL
SELECT id + @steps * 2, N'<' + dict.string_from + N'>', dict.string_to WHERE dict.string_from IN (N'<', N'>')
)
map
)
,
mapping_sequence(id, string_from, string_to) AS
(
SELECT 1, N'<', N'<<>' WHERE @from_chars LIKE N'%<%'
UNION ALL SELECT 2, N'>', N'<>>' WHERE @from_chars LIKE N'%>%'
UNION ALL SELECT 3, N'<<<>>', N'<<>' WHERE @from_chars LIKE N'%<%' AND @from_chars LIKE N'%>%'
UNION ALL SELECT 3 + id, string_from, string_to FROM two_stage
)
SELECT
@ReplaceTarget = REPLACE(@ReplaceTarget, string_from, string_to)
FROM
mapping_sequence
ORDER BY
id
;
RETURN @ReplaceTarget;
END
http://dbfiddle.uk/?rdbms=sqlserver_20172017&fiddle=8af6ae050dc8d425521ae911b70a7968一个>
http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=8af6ae050dc8d425521ae911b70a7968
或者...
http://dbfiddle.uk/?rdbms=sqlserver_201618150401704017040170404015dbfiddle.uk/一个>
http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1451aa88780463b1e7cfe15dd0071194
或者...
http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3079d4dd4289e8696072f6ee37be76ae"一个>
http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3079d4dd4289e8696072f6ee37be76ae
这篇关于SQL SERVER 中的 TRANSLATE 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
