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:
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.
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.
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)ASBEGIN
DECLARE @cPos int, @maxCPos int
SET @maxCPos = Len(@searchMap)SET @cPos = 1
WHILE @cpos <= @maxCPosBEGIN SET @sourceString = Replace(@sourceString, SubString(@searchMap, @cpos, 1), SubString(@replacementMap, @cpos, 1)) SET @cPos = @cPos + 1END
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
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).
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.
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!
ALTER FUNCTION dbo.Translate( @Source VARCHAR(8000) , @ReplaceCharOrder VARCHAR(8000) , @ReplaceWithCharOrder VARCHAR(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 @Source IS NULL RETURN NULL IF @Source = '' RETURN '' IF @ReplaceCharOrder IS NULL OR @ReplaceCharOrder = '' RETURN @Source IF @ReplaceWithCharOrder IS NULL RETURN 'Invalid parameters in function call dbo.Translate' -- -- Variables used -- DECLARE @Curr_Pos_In_Source INT , @Check_Source_Str_Len INT , @nth_source VARCHAR(1) , @Found_Match INT , @Translated_Source VARCHAR(8000) , @Match_In_ReplaceWith VARCHAR(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_SourceEND/*TESTING: SELECT dbo.Translate('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', 'abcdefghijklmnopqrstuvwxyz098765432') SELECT dbo.Translate('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', '0123456789', '9876543210') SELECT dbo.Translate('', '', '') SELECT dbo.Translate('NOMENCLATURE', 'ABCLE', '123')*/