Translate() Function Implementation in MS SQL Server

Translate Function: The translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st charact

4.6 Star
1,588 times
Add to favorites
E-mail Twitter Digg Facebook
Sign in to ask a question

  • Comments From Other Community Members
    7 Posts | Last post November 03, 2013
    • This is my first contribution to Microsoft Gallery. I would like to hear your comments regarding this.
    • Can you please also include the code here directly so I don't have to download it?
      I know of several other implementations (Brad Schulz in Handy String functions blog), so I'm curious to see the code.
    • HI Naomi ! Nice to see atleast you replied back to me and asking for code... I have put the code here so people don't have to download. I will be looking forward to hear from you of you have any advise to improve this. Also i have tested this on like 20,000 rows. I am also interested in performance testing of this with some like 1 billion rows and see how does it performs.
    • Great Stuff.. helpful for SQL guys..
    • --Clear and final perfect solution is here 
      IF OBJECT_ID (N'dbo.fn_translate', N'FN') IS NOT NULL
          DROP FUNCTION dbo.fn_translate;
      -- ============================================================      
      -- Author		:	Krushna Kadam
      -- Create date	:	4-Apr-2012
      -- Description	:	Function to return the translated value of provided diacritics.
      -- Spec ID		:	Spec #657  
      -- ============================================================      
      CREATE FUNCTION dbo.fn_translate 
      ( @string_in NVARCHAR(4000))
      DECLARE @string_1 NVARCHAR(4000)
      DECLARE @string_2 NVARCHAR(4000)
      IF (@string_1 IS NULL OR @string_2 IS NULL)
      	SET @string_1='ªµºÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜYßàáâãäåæçèéêëìíîï¿ñòóôõöùúûüÿ¿øØÞðп¿¿¿Ýý¿ÿ'
      	SET @string_2='auoAAAAAAACEEEEIIIINOOOOOUUUUYSaaaaaaaceeeeiiiiDnooooouuuuyyoODdDOoSsYyYy'
      DECLARE @string_out NVARCHAR(4000)
      DECLARE @string		CHAR(1)
      DECLARE @list TABLE (	indx	SMALLINT,
      						string	CHAR(1),
      						trans	BIT)
      IF LEN(@string_1) != LEN(@string_2)
      	SELECT @string_out = NULL
      	RETURN @string_out
      SELECT @string_out = '',
      @i = 1,
      @pos = 0
      WHILE @i <= LEN(@string_in)
      	INSERT INTO @list VALUES (@i,SUBSTRING(@string_in, @i, 1),0)
      	SELECT @i = @i + 1
      SELECT @i = 1
      WHILE @i <= LEN(@string_1)
      	UPDATE @list
      	SET string = SUBSTRING(@string_2, @i, 1),
      		trans = 1
      	WHERE ASCII(string) = ASCII(SUBSTRING(@string_1, @i, 1))
      	AND trans = 0
      	SELECT @i = @i + 1
      SELECT @pos = MAX(indx),
      @i = 1
      FROM @list
      WHILE @i <= @pos
      	SELECT @string = string
      	FROM @list
      	WHERE indx = @i
      	SELECT @string_out = @string_out + @string
      	SELECT @i = @i + 1
      RETURN @string_out
    • Naomi, I know your comment is older, but I wanted to point out that the point should be to include ONLY "sample code" in the window, NOT the entire script, because, I just tested, and the "copy-code" function does NOT COUNT as a download; and we only get points when someone "downloads" the script (via the download button). Again, I just did a "copy-code" twice on this script, and it did NOT increase the download count.
      So, unless Microsoft changes the forum to count EVERY VIEWING of a script as a download, then there's just no point in including the full script in the code window. Additionally, instead of using the "copy-code" function, the person also can just "cut-n-paste" directly out of the window, and there would be almost no way to determine such an action to be counted as a "download."
      Also, download is generally more realiable than "cut-n-paste," since cut-n-paste has been known to not always translate some characters properly (single-quote, double-quote and back-tick are some that come to mind).
      I would be interested to hear other peoples' thoughts on this; and also to hear why you (Naomi) prefer "cut-n-paste" vs. "download?"
    • Could be better