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?
For definition of function : here
TRANSLATE function in SQL SERVER
EDITED:
I'm feeling dumb - MatBailie correctly pointed out that my original solution was incorrect. I actually always thought that TRANSLATE('abc', 'abc', 'bcd') was supposed to return ddd but, after testing SQL Server 2017's TRANSLATE I see that 'bcd' would be the correct answer. You can see my original (incorrect version) by looking at this history of this post. Here's an updated solution that uses ngrams8k:
DECLARE
@string varchar(8000) = 'abc',
@fromChar varchar(100) = 'abc', -- note: no mutation
@toChar varchar(100) = 'bcd';
SELECT newString =
(
SELECT CASE WHEN x>z THEN '' WHEN x>0 THEN s ELSE t END+''
FROM dbo.ngrams8k(@string,1) ng
CROSS APPLY (VALUES (charindex(ng.token,@fromChar),len(@toChar),ng.token)) x(x,z,t)
CROSS APPLY (VALUES (ng.position, substring(@toChar,x.x,1))) xx(p,s)
ORDER BY xx.p
FOR XML PATH(''), TYPE
).value('(text())[1]', 'varchar(8000)');
Returns > bcd
That's @Payola I actually always thought ddd was the right answer and never questioned it. I posted an updated solution. –
Dato
A tweak on your answer, so as to not need ngrams8k, and work as a Single Statement Inline Table Valued Function (the fastest use of a udf that you can get afaik, though that may have changed since 2008) dbfiddle.uk/… –
Payola
I propose my translate function:
CREATE FUNCTION [dbo].[F_Translate]
(
@String varchar(8000),
@FromChar varchar(200),
@ToChar varchar(200)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @result as varchar(8000) = NULL
DECLARE @currentChar as char(1) = NULL
DECLARE @CurrentIndexFounded as int = 0
DECLARE @CurrentIndexString as int = 0
IF(@FromChar IS NULL OR @ToChar IS NULL)
BEGIN
return cast('Parameters @FromChar and @ToChar must contains 1 caracter minimum' as int);
END
ELSE IF(DATALENGTH(@FromChar) <> DATALENGTH(@ToChar) OR DATALENGTH(@FromChar) = 0)
BEGIN
return cast('Parameters @FromChar and @ToChar must contain the same number of characters (at least 1 character)' as int);
END
IF(@String IS NOT NULL)
BEGIN
SET @result = '';
WHILE(@CurrentIndexString < DATALENGTH(@String))
BEGIN
SET @CurrentIndexString = @CurrentIndexString + 1;
SET @currentChar = SUBSTRING(@String, @CurrentIndexString, 1);
SET @CurrentIndexFounded = CHARINDEX(@currentChar COLLATE Latin1_General_CS_AS, @FromChar COLLATE Latin1_General_CS_AS);
IF(@CurrentIndexFounded > 0)
BEGIN
SET @result = CONCAT(@result, SUBSTRING(@ToChar, @CurrentIndexFounded, 1)) ;
END
ELSE
BEGIN
SET @result = CONCAT(@result, @currentChar);
END
END
END
return @result
END
Are you re-inventing T-SQL's
REPLACE function? The new function TRANSLATE is used to do multiple replaces in one call. With earlier versions of SQL-Server this was often done like REPLACE(REPLACE(REPLACE(...))). Your fuction is very limited... –
Polio If you prefer use multiple replace you can. But multiple replace is not exactly the same. If you do TRANSLATE('ABC', 'ABC', 'BCD') your result must be BCD and not DDD. Translate is really necessary sometime (Microsoft add in 2016 verson...) –
Kandis
By exemple, try to check if a string contain only A-Z you can do it: where translate(mycolumn, 'ABCDEFGIJKLMNOPQRSTUVWXYZ', ' ')<>'' –
Kandis
you are reinventing
patindex? –
Polio no, already patindex return a position, not replacement letter by letter –
Kandis
the example you give above (Check for A-Z) is better and faster solved with
patindex or like... Sounds like the XY-problem ... –
Polio Better than a WHILE loop is - at least in my eyes - the quirky update wrapped in a function:
You can maintain replace values in a table. You might add some grouping keys (e.g. for languag selection or topic focus) and pass this into the function as additional parameter:
CREATE TABLE ReplaceValues (FindChar NVARCHAR(100) NOT NULL
,ReplWith NVARCHAR(100) NOT NULL
,SortOrder INT NOT NULL);
INSERT INTO ReplaceValues VALUES('a','x',1) --all "a" will be "x"
,('test','yeah!',2) --"test" will be "yeah"
,('hello','ciao',3) --"hello" will be "ciao"
,('xxx','magic',4); --this is magic (see below)
GO
--You cannot use the quirky update inlined, but you can wrap it within a scalar function:
CREATE FUNCTION dbo.MultiReplace(@ReplaceTarget VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
--Quirky Update: One of the rare situations where this is a good idea
SELECT @ReplaceTarget=REPLACE(@ReplaceTarget,rv.FindChar,rv.ReplWith)
FROM ReplaceValues AS rv
ORDER BY rv.SortOrder;
RETURN @ReplaceTarget;
END
GO
--A table with test data
declare @t table(TestString varchar(100))
insert into @t values('This string is without repls')
,('This is a test, hello, one more test')
,('See the cascade replace with aaa, which is converted to xxx, then to magic');
--...and the magic is going in here:
SELECT t.TestString
,dbo.MultiReplace(t.TestString) AS Translated
FROM @t AS t
GO
--Clean-Up
DROP FUNCTION dbo.MultiReplace;
DROP TABLE ReplaceValues;
The result
This string is without repls
This is x yeah!, ciao, one more yeah!
See the cxscxde replxce with magic, which is converted to magic, then to mxgic
Not the same to translate method –
Kandis
Nice... dbfiddle.uk/… –
Payola
Upgrade server not always easy... And i have specified SQL Server in my question. And use a table like this is Dangerous. Multiple person can use this function in same time. –
Kandis
And his method dont have same comportement of translate definition (ISO). If you insert a replacement of A by B, B by C and C by D, when you replace ABC your result is not BCD but DDD –
Kandis
Like this dbfiddle.uk/… –
Kandis
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
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
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_2017&fiddle=8af6ae050dc8d425521ae911b70a7968
Or...
http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1451aa88780463b1e7cfe15dd0071194
Or...
http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3079d4dd4289e8696072f6ee37be76ae
Thank you but doest work for same resaon. replace ABC by BCD give DDD and not BCD dbfiddle.uk/… –
Kandis
Then be careful with you own use of the function??? dbfiddle.uk/… –
Payola
This function works in this way under Oracle, DB2 and PosgreSQL;) –
Kandis
And SQL Server 2017 ... dbfiddle.uk/… –
Kandis
That's why I proposed this function for those who like me need this function and can not change (for a time) SQL Server version –
Kandis
Then you really need to jump through some dangerous / inane hoops... This is where I'm at, and it doesn't quite work yet (replacing a
/ will break it)... dbfiddle.uk/… –
Payola Why "inept"? It's a method like any other and it has the merit of working properly. it's a bit purist as an answer no? I am not sure that using multiple "replace" in multiple "select" is more efficient. Actually a replacement of / does not work in your method and what is more your method does not consider also the case –
Kandis
replace space by an other character doesnt work too with your method dbfiddle.uk/… –
Kandis
I said "inane", not "inept". Here is my latest itteration : dbfiddle.uk/… (It works with spaces, it seems to work with slashes, it works with
abc => bcd, but you better test it to hell and back before relying on it...) –
Payola Let us continue this discussion in chat. –
Kandis
Another revision : dbfiddle.uk/… –
Payola
inept or stupid it's a semantic question ... I put +1 because it works (we must salute the effort, free to do the same). However, I have doubts about the performances. And in my opinion, it also lacks error handling to have a behavior similar to the method defined on other types of database. Normally it should not be possible to make a replacement with different input and output lengths or to replace the same character several times or have null into fromchar or tochar in having an error message. –
Kandis
@Kandis - Then put the full, complete and exact requirements in your question. (
Inane is neither inept or stupid, it's foolish or crazy or silly, in this case because it incurs a large overhead, can be worked around to be avoided, and is extremely difficult to reliably test.) –
Payola In short we will not procrastinate on a qualifier (English is not my language) nor on whom is the most perfomant it has no use. As for the details I agree, although I still specified 'function equivalent to the standard function TRANSLATE'. But it's still a matter of logic that if we do letter-to-letter replacement the lengths must be the same. –
Kandis
Add your own error handling then, and remove the need for
AND id < LEN('_' + @to_chars + '_') - 1. You may speak French, but that doesn't stop you from writing SQL yourself. (If you don't want to argue about Inane, don't mis-quote me, and don't bring it up [three times now] yourself...) –
Payola Really, what is the interest of using this type of qualifier (inane), it is a value judgment that brings nothing? And what is the problem of using a while rather than using an external table (that's foolish), or replace (which are actually only while mulitple) and multiple select which, whatever you think are much more expensive than the while in this case .... and what connection with writing SQL myself? Translate is an ISO function and I have not defined how it works ... –
Kandis
A loop in T-SQL is orders of magnitudes slower than the internal loops of the RDBMS's engine. (I'm done improving this answer, you're an ingrate.) –
Payola
@Payola , i'm not ingrate, i have up your answer (not you on mine...) –
Kandis
@Shnugo, Sorry, I did not want to annoy you. I wanted to give an example of logic on what was hailed as a good answer and was not. That said, you do not teach me about derived tables that you do not use either in your proposal, which is why I pointed it out. Note that your proposal does not work. When to politeness, in any way I was deliberately rude, I concede that my English do not have the best, but I did not call anything arbitrary. –
Kandis
Your first function is good. You should include some DDL for your numbers table and mention that it's 0-based. Someone with a numbers table that begins at 1 (such as mine) will get the wrong answer. 2nd - you could lose
AND id < LEN(@to_chars) - the db2 version of translate allows for you to input a @from_chars that's longer than @to_chars; the trailing characters are just dropped: note: dbo.MultiReplace('ab00','ab0,'xx') should return "xx" not "xx00". –
Dato Thought I'd put my idea in as well. This avoids the dreaded WHILE loop, and, also, doesn't use a self referencing variable (which can get ugly).
Note the use of a Tally table, first, and then I use a Table Valued Function (rather than Scalar, which are slow) to do the work.
Note, that I have set it so that if you provide fewer arguments on the right hand side, that the character will be removed. So, if the parameter @FindChars had the value 'AB' and @ReplaceChars the value 'C', then 'A' would be replaced with 'C' and 'B' would be replaced with ''. I note that with TRANSLATE this would produce the error The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.
The problem, however, with a function is that you can't use things like THROW or RAISERROR. This means that actually producing an error inside a function isn't possible. You could, however, set something up so that NULL is returned if the two lengths don't match, but (unfortunately) the error production cannot be performed inside the function itself.
CREATE VIEW dbo.Tally WITH SCHEMABINDING
AS
WITH C1 AS (SELECT 1 AS I UNION ALL SELECT 1),
C2 AS (SELECT 1 AS I FROM C1 AS L CROSS JOIN C1 AS R),
C3 AS (SELECT 1 AS I FROM C2 AS L CROSS JOIN C2 AS R),
C4 AS (SELECT 1 AS I FROM C3 AS L CROSS JOIN C3 AS R),
C5 AS (SELECT 1 AS I FROM C4 AS L CROSS JOIN C4 AS R),
C6 AS (SELECT 1 AS I FROM C5 AS L CROSS JOIN C5 AS R),
RN AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM C6)
SELECT TOP (1000000) N
FROM RN
ORDER BY N;
GO
CREATE FUNCTION dbo.OwnTranslate (@String varchar(8000),@FindChars varchar(8000), @ReplaceChars varchar(8000))
RETURNS TABLE
AS RETURN (
WITH ToBeReplaced AS (
SELECT @String AS String,
FC.N,
SUBSTRING(@FindChars, FC.N,1) AS FC,
ISNULL(SUBSTRING(@ReplaceChars, RC.N,1),'') AS RC
FROM (SELECT TOP (LEN(@FindChars)) N FROM Tally) FC
OUTER APPLY (SELECT TOP (LEN(@ReplaceChars)) T.N FROM Tally T WHERE T.N = FC.N AND T.N <= LEN(@ReplaceChars)) RC),
Replacing AS (
SELECT N, REPLACE(String, FC, RC) AS ReplacedString
FROM ToBeReplaced
WHERE N = 1
UNION ALL
SELECT R.N + 1, REPLACE(ReplacedString, TBR.FC, TBR.RC) AS ReplacedString
FROM ToBeReplaced TBR
JOIN Replacing R ON TBR.N = R.N + 1)
SELECT TOP 1 ReplacedString
FROM Replacing
ORDER BY N DESC);
GO
WITH VTE AS (
SELECT *
FROM (VALUES ('This is a string to be Translated.')) V(S))
SELECT VTE.S, OT.ReplacedString
FROM VTE
CROSS APPLY dbo.OwnTranslate (VTE.S, 'Ts ', 'qz_') OT;
GO
--Clean up
DROP FUNCTION dbo.OwnTranslate;
DROP VIEW Tally;
Any questions, please do ask.
for raise error you can do like in my proposition too, it's a tips but it's work –
Kandis
Not sure what your proposition on that was? I note your answer, however, uses a
WHILE which is something to avoid; the performance would be quite slow with a larger dataset. –
Gloucestershire Might need to set a maxrecursion 0 in there? –
Payola
Can't test this on my phone, but doesn't it have the same "feature" as the others where
TRANSLATE('abc', 'abc', 'bcd') comes out as 'ddd' but the OP needs 'bcd'? –
Payola @Payola Yes, I see your point. Will have a think on further solution tomorrow. Unlikely to, however, need to use
OPTION (MAXRECURSION 0) or similar, that would mean that you're supplying over 100 characters to translate. I'd see that as unlikely. Even your using an AS and CS collation, thats 26 x 2 letters + 10 numbers (62), leaving 38 recursions for accented letters or special characters. Also, if you were to supply over 100 characters to translate, OPTION would need to be supplied in the query using the function, not the function itself. –
Gloucestershire At least then limit the params to 100 chars ;) –
Payola
In needing to remove/replace numeric values and characters such as single vs double quotes on SQL 2016, I have a utility function that does a translate() as an iTVF without CTE recursion. While developing, I discovered surrogate pairs - which, while unlikely in my use case are accounted for in the following sample.
CREATE OR ALTER FUNCTION util.__string_ReturnOnlyAlpha (@input NVARCHAR(MAX))
RETURNS TABLE
AS RETURN
(
SELECT --(xml).value , remove entities from
(SELECT [value] FROM (
SELECT TOP(LEN(@input))
n.[i] , SUBSTRING(@input,n.[i],1) [value]
FROM ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT '.')) [i] FROM
STRING_SPLIT(REPLICATE(CAST('.' AS VARCHAR(MAX)),LEN(@input) - 1),'.')
) n([i])
ORDER BY n.[i]
) ss WHERE ss.[Value] IN (' ') OR ss.[value] LIKE '%[A-Za-z]%'
FOR XML PATH(''),type
).value('.','nvarchar(max)') [TextValue]
)
GO
GO
/* Translate pt 1/3 : */
CREATE OR ALTER FUNCTION util.__string_AsCharacterRows(@input NVARCHAR(MAX))
RETURNS TABLE
AS RETURN
(
SELECT TOP(LEN(@input COLLATE Latin1_General_100_CI_AS_KS_WS_SC))
n.[i] , CAST(SUBSTRING(@input COLLATE Latin1_General_100_CI_AS_KS_WS_SC,n.[i],1) AS NVARCHAR(2)) [value]
FROM ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT '.')) [i] FROM
STRING_SPLIT(REPLICATE(CAST('.' AS VARCHAR(MAX)),LEN(@input) - 1),'.')
) n([i])
ORDER BY n.[i]
)
GO
GO
/* Translate pt 2/3 : */
CREATE OR ALTER FUNCTION util.__string__TranslateMap(@inputString NVARCHAR(MAX),@characters NVARCHAR(MAX),@translations NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(
SELECT TOP(LEN(@inputString COLLATE Latin1_General_100_CI_AS_KS_WS_SC))
s.[i],
ISNULL(ct.t,s.[value]) [value]
FROM
util.__string_AsCharacterRows(@inputString) s
LEFT HASH JOIN (SELECT
c.i,
c.[value] c,
t.[value] t
FROM util.__string_AsCharacterRows(@characters) c
INNER MERGE JOIN util.__string_AsCharacterRows(@translations) t ON t.i = c.i
) ct ON ct.c = s.[value]
ORDER BY
s.[i]
)
GO
GO
/* Translate pt 3/3 : */
CREATE OR ALTER FUNCTION util.__string__Translate(@inputString NVARCHAR(MAX),@characters NVARCHAR(MAX),@translations NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN -- if you have a CLR method for string_agg replacement, use that here.
( SELECT
(SELECT [value] FROM
util.__string__TranslateMap(@inputString,@characters,@translations)
FOR XML PATH(''),type
).value('.','nvarchar(max)') [TextValue]
--but if you have CLR and can add new methods, maybe use a CLR translate function
)
GO
GO
/*example*/
SELECT
input.string, -- in: '😃' & 👍.
translated.TextValue -- out: "😎" & ✌!
FROM
(VALUES(N'''😃'' & 👍. ')
,(REPLICATE(N'''😃'' & 👍. ',9000))) input(string) --can handle characters over 9000!
CROSS APPLY util.__string__Translate(input.string,N'''😃👍.',N'"😎✌!') translated
This uses 3 iTVFs that are wrappers and reuse of the one before, but you could probably write it out as subqueries if needed. Memory Usage is not optimized and if needed, you may want to wait for 2017 or CLR.
© 2022 - 2025 — McMap. All rights reserved.
TRANSLATE('abc', 'abc', 'bcd')='bcd'rather than'ddd'? – Payola