T-SQL Equivalent to Oracle TRANSLATE
-
Hi, everyone. I'm converting a SQL script from our old Oracle-based Lawson application to our new SQL Server-based PeopleSoft app. This script uses the TRANSLATE function to convert punctuation marks like '/', '#','&', etc to tildes as follows:
TRANSLATE(detail.description,'\/&*?+#;<>",','~~~~~~~~~~~~')
I need to do the same thing in the new SQL Server script but can't find any T-SQL function that works the same way. Is there an equivalent function in T-SQL?
I know that I could use the T-SQL REPLACE function, but that looks for substrings not individual characters, and I'd rather not have to do some ungodly nested call like this:
REPLACE(REPLACE(REPLACE(REPLACE(coa.descr,'\','~'),'/','~'),'&','~'),'*','~') ... etc
Thanks in advance for any help.
Jose'
-
Hey Jose
SQL Server does not have an equivelent to TRANSLATE.. my only peeve with SQL Server. Oracle's Translate is also super useful for datetime to string conversion. DO YOU HEAR ME MICROSOFT?!
REPLICATE wont do what you are looking for either, it just repeats a string x times.
Julian Kuiters
juliankuiters.id.au -
As the others have said, nothing like Translate exists is SQL Server. You'll have to write inline code to do it, or use a UDF such as the following:
-- TRANSLATE(detail.description,'\/&*?+#;<>",','~~~~~~~~~~~~')
-- SELECT dbo.fnTranslate('This/\ is&*?a t+e#s;t', '\/&*?+#;<>",','~~~~~~~~~~~~' )
CREATE FUNCTION dbo.fnTranslate
(
@sourceString varchar(8000),
@searchMap varchar(8000),
@replacementMap varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @cPos int, @maxCPos int
SET @maxCPos = Len(@searchMap)
SET @cPos = 1
WHILE @cpos <= @maxCPos
BEGIN
SET @sourceString = Replace(@sourceString, SubString(@searchMap, @cpos, 1), SubString(@replacementMap, @cpos, 1))
SET @cPos = @cPos + 1
END
RETURN @sourceString
END
-
Looks like I was beaten to it by mkeast, but for what it's worth here's my version
CREATE FUNCTION dbo.Translate (@source varChar(8000),@replaceRequired varChar(200),@replaceWith varChar(10))
RETURNS varchar(8000) AS
BEGIN
declare @i int
declare @checkLen int
select @checkLen = Len(@replaceRequired) +1
set @i = 1
while @i < @checkLen
begin
select @source = replace(@source,substring(@replaceRequired,@i,1),@replaceWith)
set @i = @i+1
end
Return @source
END
-
Thanks, WSquared, for your suggestion. And thanks to everyone else for the ideas!
Let me throw an question out there to anyone else who has had to migrate from Oracle to SQL Server -- does it seem to you that SQL Server is not as mature a product, at least as far as the SQL language extensions go? I'm not trying to start a religious war here, but I have already run into several features that SQL-Plus or PL-SQL has that T-SQL does not have. Some examples are the Oracle TRANSLATE function, the ACCEPT statement (i.e. the ability to get user input), and the SPOOL statement. Maybe my exposure to T-SQL is still too limited to make a fair assessment, but so far my impression of the language itself hasn't been that positive. (I do like the Query Analyzer environment, though).
Any thoughts?
Thanks again,
Jose'
-
Jose
There are major differences between Oracle and SQL Server. I don't know if it's a maturity thing as such, just the way Microsoft does things. Oracle seems (to me, personal opinion) to have thousands of highly configurable options, but no simple answers. SQL Server is the other end of the spectrum, where almost everything is configurable in the GUI, and not much under-the-bonnet stuff is exposed. SQL Server is like Windows. Oracle is like unix/linux.
T-SQL however seems to be massively lacking in features, by comparison VBScript is more useful. But Microsoft seems to be addressing this lack of features by deciding to let you write your own code in 2005 with .Net. When that comes around (so long as it's not like DTS) you should be able to do anything you want inside of SQL Server, no matter how bad. I expect to see some wonderfully bad ideas become reality then.
Oracle came out of the box without much configuration, and ran real slow till we told it to use more than 20MB memory. SQL Server came out of the box and did everything it could to run optimally from the start.
It's also interesting that the Oracle DBA's I know earn a lot more than SQL DBA's. But, they also have to attend numerous courses to be able to better understand Oracle. SQL Server is something that most DBA's seem to learn on the fly, stretching their wings as they go.
Oracle is something I can definately see being useful in really large environments. SQL Server could scale to handle the same databases, but earns its stripes by being easy to install, configure, and leave alone.
Julian Kuiters
juliankuiters.id.au -
Thanks, Julian, for providing some perspective. That is definitely good to know! While writing my last post I was a little annoyed (could you tell?) because of the T-SQL limitations I was running into. The lack of ACCEPT or SPOOL equivalents, especially, seem to be a pretty glaring hole in T-SQL. We've got tons of standalone scripts for our Lawson Oracle database which we will have to be converting to run under PeopleSoft / SQL Server and a high percentage of the existing scripts make use of features that don't easily translate. I'll have to write some sort of front-end to get the user input params and utilize ODBC, most likely. Should be interesting...
Thanks again for your insight!
Jose'
-
This is what we use:
ALTER FUNCTION dbo.Translate
( @SourceVARCHAR(8000)
, @ReplaceCharOrder VARCHAR(8000)
, @ReplaceWithCharOrderVARCHAR(8000)
)
RETURNS VARCHAR(8000) AS
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Object Name:dbo.Translate
Author:UB for DCF, on August05, 2008
Purpose:Like TRANSLATE function in Oracle. Charecter-wise replace in source string with given charecters.
Input:
Output:returns @Translated_Source string
Version:1.0 as of 08/05/2008
Modification:
Execute:SELECT dbo.Translate('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', '1234567890', '0987654321')
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
BEGIN
--
--Validate input
--
IF@SourceIS NULL
RETURN NULL
IF @Source= ''
RETURN ''
IF @ReplaceCharOrder IS NULLOR
@ReplaceCharOrder = ''
RETURN @Source
IF@ReplaceWithCharOrder IS NULL
RETURN 'Invalid parameters in function call dbo.Translate'
--
--Variables used
--
DECLARE @Curr_Pos_In_SourceINT
, @Check_Source_Str_LenINT
, @nth_sourceVARCHAR(1)
, @Found_MatchINT
, @Translated_SourceVARCHAR(8000)
, @Match_In_ReplaceWithVARCHAR(1)
--
--Assign starting values for variables
--
SELECT @Curr_Pos_In_Source= 1
, @Check_Source_Str_Len= LEN(@Source)
, @Translated_Source= ''
--
--Replace each charecter with its corrosponding charecter from @ReplaceWithCharOrder
--
WHILE @Curr_Pos_In_Source <= @Check_Source_Str_Len
BEGIN
--
--Get the n'th charecter in @Source
--
SELECT @nth_source = SUBSTRING(@Source, @Curr_Pos_In_Source, 1)
--
--See if there is a matching character for @nth_source in the @ReplaceCharOrder String, then replace it with
--corrosponding character in @ReplaceWithCharOrder String. If not..go to next n'th character in @Source
--If a match is found in @ReplaceCharOrder but no corrosponding character in @ReplaceWithCharOrder
--then, replace it with '' (nothing)
--Store the resultant string in a separate variable
--
SELECT @Found_Match = CHARINDEX(@nth_source, @ReplaceCharOrder COLLATE SQL_Latin1_General_CP1_CS_AS)
IF @Found_Match > 0
BEGIN
--
--Finding corrosponding match in the @Found_Match'th position in @ReplaceWithCharOrder
--if not found then replace it with '' (nothing)
--
SELECT @Match_In_ReplaceWith = SUBSTRING(@ReplaceWithCharOrder, @Found_Match, 1)
--
--Now replace @nth_source with @Match_In_ReplaceWith and store it in @Translated_Source
--
SELECT @Translated_Source = @Translated_Source + @Match_In_ReplaceWith
END
ELSE
BEGIN
--
--No match found in @ReplaceCharOrder
--
SELECT @Translated_Source = @Translated_Source + @nth_source
END
--
--Increment the current position for loop
--
SELECT @Curr_Pos_In_Source = @Curr_Pos_In_Source + 1
END
RETURN @Translated_Source
END
/*
TESTING:
SELECT dbo.Translate('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', 'abcdefghijklmnopqrstuvwxyz098765432')
SELECT dbo.Translate('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', '0123456789', '9876543210')
SELECT dbo.Translate('', '', '')
SELECT dbo.Translate('NOMENCLATURE', 'ABCLE', '123')
*/
-
Thanks for sharing that, UB.
Regards,
José
-
I see usage in this UDF for one way data obfuscation possibly by using the RAND object to randomize the ouput character ensuring the the output of RAND stays within the ASCII code range thererby giving anonymity to the data but preserving sentence like structure when converting text fields. This could be used to send data to third party for testing.
-
SSC Eights!,
inside the replace, I think you want
substring(@replaceWith,@i,1)
rather than
@replaceWith
-
Forgive me for reviving an 8 year old forum but here's a brand new, well tested and high-performing translate function: Translate8K[/url]
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Opening a old thread for the benifit of someone landing here via a google search.
Translate now does exist in SQL Server 2017 🙂
Regards,
http://Strictlysql.blogspot.com
Raj - arr.nagaraj - Saturday, January 20, 2018 12:45 AM
Let's hope they didn't use the same technology as they did with FORMAT, which is 44 times slower than using native or brute force methods.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply