T-SQL Equivalent to Oracle TRANSLATE

  • Jose Johnson

    SSC Eights!

    Points: 989

    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

    SSChampion

    Points: 11752

    Try REPLICATE.


    Kindest Regards,

  • Julian Kuiters

    SSCertifiable

    Points: 6555

    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

    SSChampion

    Points: 11132

    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

    SSCrazy

    Points: 2363

    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

    SSC Eights!

    Points: 989

    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

    SSCertifiable

    Points: 6555

    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

    SSC Eights!

    Points: 989

    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

    SSCrazy

    Points: 2987

    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')

    */

  • Jose Johnson

    SSC Eights!

    Points: 989

    Thanks for sharing that, UB.

    Regards,

    José

  • Stephen Yale

    SSC Veteran

    Points: 266

    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.

  • Philip Campbell

    SSC Enthusiast

    Points: 123

    SSC Eights!,

    inside the replace, I think you want

    substring(@replaceWith,@i,1)

    rather than

    @replaceWith

  • Alan Burstein

    SSC Guru

    Points: 61006

    Forgive me for reviving an 8 year old forum but here's a brand new, well tested and high-performing translate function: Translate8K[/url]

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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

  • arr.nagaraj

    SSCertifiable

    Points: 6518

    Opening a old thread for the benifit of someone landing here via a google search.

    Translate now does exist in SQL Server 2017 🙂

  • Jeff Moden

    SSC Guru

    Points: 993644

    arr.nagaraj - Saturday, January 20, 2018 12:45 AM

    Opening a old thread for the benifit of someone landing here via a google search.

    Translate now does exist in SQL Server 2017 🙂

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply