Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2006
    Posts
    2

    Unanswered: Oracle Translate function equivalent in SQL Server

    Hi

    I want to know the equivalent of the Oracle translate function in SQL Server.

    eg : select translate('entertain', 'et', 'ab') from dual.

    I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'.

    Please let me know if there is some other equivalent function in SQL Server

    thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    CASE works wonders.

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, i would just love to see how CASE works in this, er, um, case

    care to share the example for TRANSLATE('entertain', 'et', 'ab'), pat?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No. I'm tired, cranky, and trying to help. What are you doing to help?

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    What are you doing to help?
    subtly trying to inform the original poster that pursuing CASE may be a waste of time until he sees an actual example (which i am having a hard time conceiving)

    i merely tried to match the degree of subtlety in my reply to yours

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Maybe I am being a thicky pants but do nested REPLACEs not do the trick?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thicky pants???

    yes, that's the way i'd do it, as many nested REPLACE functions as characters to be translated

    here's a classic example of TRANSLATE being used for simple encryption --

    SELECT TRANSLATE(mycolumn,'abcdefghijklmnopqrstuvxyz', '5869413270plokij^#jm![edxc')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Gotta love that Friday Feeling:
    Code:
    CREATE FUNCTION dbo.Thicky_Pants 
     (
     @Input AS VarChar(1000),
     @Find AS VarChar(100),
     @Replace AS VarChar(100)
     )
     RETURNS VarChar(1000)
    AS
    BEGIN
     
     DECLARE @i AS TinyInt
     
     SELECT @i = 1
     
     WHILE @i <= LEN(@Find) BEGIN
      
      SELECT @Input = REPLACE(@Input, SUBSTRING(@Find, @i, 1), SUBSTRING(@Replace, @i, 1))
      SELECT @i = @i + 1
     
     END
     RETURN @Input
    END
    GO
     
    DECLARE @String AS VarChar(1000)
     
    SELECT @String = 'pootle_flump'
    SELECT @String = dbo.Thicky_Pants(@String, 'pt', 'xz')
     
    PRINT  @String
    Quote Originally Posted by SQL Server
    xoozle_flumx
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937
    here's a classic example of TRANSLATE being used for simple encryption --

    SELECT TRANSLATE(mycolumn,'abcdefghijklmnopqrstuvxyz', '5869413270plokij^#jm![edxc')
    Unless you are developing an application to write cryptograms for the Sunday paper, I hope nobody would use such an algorithm.

    TRANSLATE is one of the goofier built-in functions in Oracle. The laser was once described as "a solution in search of a problem". Maybe someday there will be a practical use for TRANSLATE as well. In 10 years of SQL Server programming I've never found a need for such a function.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    In 10 years of SQL Server programming I've never found a need for such a function.
    well, there's your answer -- you need it all the time in oracle
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Somehow I've manager to get through a few Oracle projects without using it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jun 2011
    Posts
    2

    stripping non-numeric characters

    Quote Originally Posted by blindman View Post
    Unless you are developing an application to write cryptograms for the Sunday paper, I hope nobody would use such an algorithm.

    TRANSLATE is one of the goofier built-in functions in Oracle. The laser was once described as "a solution in search of a problem". Maybe someday there will be a practical use for TRANSLATE as well. In 10 years of SQL Server programming I've never found a need for such a function.
    I am using TRANSLATE() for a liberal conversion query to convert NVARCHAR2 values to NUMBER values. I use it to strip all "non-numeric related" characters from a string and convert the remaining to a number. So strings like "$25.00", "SS#: 444-55-5555", etc. will convert.

    I'm looking for some way to do it in Transact SQL (without writing proc) if possible.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i wonder whether, five years later, pat is still tired and cranky

    maybe he could whip up your answer using CASE to strip out all non-numeric characters

    it'd have to be a pretty hefty CASE, though

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2011
    Posts
    2
    Ha!

    Long live the thread...!

    Still interested in solutions if there is a compact way to do it.

    Regards,

    -SB

  15. #15
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Still interested in solutions if there is a compact way to do it.
    I came up with this solution a while ago. It's not perfect (not fast), but in the batch process where it is used, those extra 10 seconds don't matter.
    Code:
    IF OBJECT_ID (N'dbo.Translate', N'FN') IS NOT NULL
    	DROP FUNCTION dbo.Translate
    GO
    
    -- This UDF Translate mimics the DB2 system provided Translate() function
    -- The purpose of this UDF is removing "strange" characters from names of French (, ), Eastern-Europe (), 
    -- Scandinavian (), ... students, before passing them to the Government. We kept getting errors back due to 
    -- the way those characters are stored in their mainframe.
    -- TO DO: This UDF is functional but not fast. If higher speed is required, rewrite it in CLR.
    CREATE FUNCTION dbo.Translate (
    	@String NVARCHAR(500),		-- The string from which you want certain characters to be replaced or removed
    	@SearchFor NVARCHAR(100),	-- A string of characters you want to be replaced or removed, like ''
    	@ReplaceBy NVARCHAR(100)	-- A string of character you want the @SearchFor characters be replaced with.
    								-- A character with index i in @SearchFor is replaced with the character with 
    								-- index i in @ReplaceBy.
    								-- When there is no corresponding @ReplaceBy character at index i (there are more 
    								-- characters in @SearchFor than in @ReplaceBy), the @SearchFor character is removed
    )
    RETURNS NVARCHAR(500)
    AS
    BEGIN
    	if @String IS NULL
    		RETURN(NULL)
    
    	if @SearchFor IS NULL AND @ReplaceBy IS NULL
    	BEGIN	-- provide some standard 
    		SET @SearchFor = ''
    		SET @ReplaceBy = 'aaaaaeieieiicanoooooaouuuuyAAAAAEIEIEIIANOOOOOAOUUUUY'
    	END
    	ELSE 
    	BEGIN 
    		if @ReplaceBy IS NULL
    			RETURN(NULL)
    
    		if @SearchFor IS NULL
    			RETURN(NULL)
    	END
    
    -- Suppose a character 'X' is present (one time or multiple times) in @String AND in @SearchFor. 
    -- And the index of the character 'X' in @SearchFor is I. 
    -- The function will replace all occurrences of the character 'X' in @String with the corresponding 
    -- character Y at the index I in @ReplaceBy.
    -- When no characters of @SearchFor are present in @String, the function returns @String unaltered.
    -- When there are more characters in @SearchFor than in @ReplaceBy (I > len(@ReplaceBy)), those extra 
    -- characters in @SearchFor are removed from @String (replaced by an empty string). 
    -- There may not be more characters in @ReplaceBy than in @SearchFor. 
    	if len(@ReplaceBy) > len(@SearchFor)
    		RETURN(NULL)
    
    	DECLARE @SearchForIndex INT
    	DECLARE @SearchChar NVARCHAR(1)
    	DECLARE @ReplaceChar NVARCHAR(1)
    
    	SET @SearchForIndex = 1
    
    	-- for all characters in @SearchFor
    	WHILE @SearchForIndex <= len(@SearchFor)
    	BEGIN
    		-- get the current character from @SearchFor
    		SET @SearchChar = Substring(@SearchFor, @SearchForIndex, 1)
    		-- If the current @SearchFor character is also present in @String
    		-- Use a case sensitive collation, otherwise 'Franois' gets translated to 'FRANCOIS'
    		if @String COLLATE Latin1_General_BIN LIKE '%'+@SearchChar+'%'
    		begin
    			-- Replace all occurrences of the current @SearchFor character in @String
    			-- by the corresponding character (same index) in @ReplaceBy.
    			-- BUT when there is no corresponding character in @ReplaceBy 
    			-- (@SearchForIndex > len(@ReplaceBy)), replace the @SearchFor character in @String 
    			-- in @String by the empty string '' (remove them)
    			if @SearchForIndex <= len(@ReplaceBy) 
    				SET @ReplaceChar = Substring(@ReplaceBy, @SearchForIndex, 1)
    			else
    				SET @ReplaceChar = ''
    
    			SET @String = REPLACE(@String COLLATE Latin1_General_BIN, @SearchChar, @ReplaceChar)
    		end
    		SET @SearchForIndex = @SearchForIndex + 1
    	END
    	RETURN(@String)
    END
    GO
    
    
    -- some tests
    SELECT dbo.Translate('123abcABC1', '1', 'E') AS Translated -- 'E23abcABCE' OK
    SELECT dbo.Translate('123abcABC1', '123', 'E') AS Translated -- 'EabcABCE' OK
    SELECT dbo.Translate('123abcABC1', 'mno', 'XYZ') AS Translated -- '123abcABC1' OK
    SELECT dbo.Translate('123abcABC1', 'ABC', 'XYZ') AS Translated -- '123abcXYZ1' OK
    SELECT dbo.Translate('123abcABC1', 'ABC', '') AS Translated -- '123abc1' OK
    SELECT dbo.Translate('123abABC1', null, null) AS Translated -- '123abc1' OK
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •