Convert VBA function to SQL function

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  May 7, 2020 at 4:57 pm

  #3749705

  Okay - I see the problem here.  The issue is that we are replacing a single character from Viet to multiple characters in VNI.  The way the replace works it ends up performing a replace on 'Á' which becomes 'AÙ' and then replaces the character 'Ù' which becomes 'UØ'.

  To fix this - we have to change the function so it replaces each character position by position.

   Create Function dbo.test_uni_vni_replace (
          @string nvarchar(max)
          )
  Returns nvarchar(max)
       As
    Begin
  
  Declare @replaceString nvarchar(max) = '';
  
   --==== Convert the input string from VNI to Viet (unicode_hex)
     With t(n)
       As (
   Select t.n 
     From (
   Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
          )
        , iTally (n)
       As (
   Select Top (len(@string)) 
          checksum(row_number() over(Order By @@spid)) 
     From t t1, t t2, t t3, t t4, t t5, t t6
          )
   Select @replaceString += coalesce(e.vni, substring(@string, it.n, 1))
     From iTally                                           it
    Outer Apply (Select *
                   From dbo.viet_character_encoding       vce
                  Where vce.viet = substring(@string collate Latin1_General_CS_AS, it.n, 1)) As e 
  
   Return @replaceString;
      End
       Go
  

  Try this - it should work but I don't have a lot of test data to work with.

  This is setup to use nvarchar(max) - it will perform better if we can use nvarchar(4000)

   Create Function dbo.test_uni_vni_replace (
          @string nvarchar(4000)
          )
  Returns nvarchar(max)
       As
    Begin
  
  Declare @replaceString nvarchar(max) = '';
  
   --==== Convert the input string from VNI to Viet (unicode_hex)
     With t(n)
       As (
   Select t.n 
     From (
   Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
          )
        , iTally (n)
       As (
   Select Top (len(@string)) 
          checksum(row_number() over(Order By @@spid)) 
     From t t1, t t2, t t3
          )
   Select @replaceString += coalesce(e.vni, substring(@string, it.n, 1))
     From iTally                                           it
    Outer Apply (Select *
                   From dbo.viet_character_encoding       vce
                  Where vce.viet = substring(@string collate Latin1_General_CS_AS, it.n, 1)) As e 
  
   Return @replaceString;
      End
       Go

  • This reply was modified 4 years, 6 months ago by  Jeffrey Williams. Reason: Modified the second function - need to return nvarchar(max) and set @replaceString to nvarchar(max) to handle converted strings that are larger than 4000 characters

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs