Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Equivalent to Oracle TRANSLATE


T-SQL Equivalent to Oracle TRANSLATE

Author
Message
Jose Johnson
Jose Johnson
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 19

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'


TriggerMe
TriggerMe
Ten Centuries
Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)

Group: General Forum Members
Points: 1000 Visits: 328
Try REPLICATE.


Kindest Regards,



Julian Kuiters
Julian Kuiters
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 1

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
vadba
vadba
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1179 Visits: 443

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





WSquared
WSquared
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1398 Visits: 286

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





Jose Johnson
Jose Johnson
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 19

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'


Julian Kuiters
Julian Kuiters
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 1

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
Jose Johnson
Jose Johnson
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 19

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'


_UB
_UB
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 440
This is what we use:

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_Source

END



/*
TESTING:
SELECT dbo.Translate('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', 'abcdefghijklmnopqrstuvwxyz098765432')
SELECT dbo.Translate('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', '0123456789', '9876543210')
SELECT dbo.Translate('', '', '')
SELECT dbo.Translate('NOMENCLATURE', 'ABCLE', '123')


*/



SqlTimes Blog on Sql Server
Jose Johnson
Jose Johnson
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 19
Thanks for sharing that, UB.

Regards,
José
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search