Drupal Functions for MSSQL

  1. USE [YourDatabaseName]
  2. GO
  3.  
  4. SET ANSI_NULLS ON
  5. GO
  6.  
  7. SET QUOTED_IDENTIFIER ON
  8. GO
  9.  
  10. CREATE FUNCTION [dbo].[CONCAT](@op1 sql_variant, @op2 sql_variant) RETURNS nvarchar(4000) AS
  11. BEGIN
  12. DECLARE @RESULT nvarchar(4000)
  13. SET @RESULT = CAST(@op1 AS nvarchar(4000)) + CAST(@op2 AS nvarchar(4000))
  14. RETURN @RESULT
  15. END
  16. GO
  17.  
  18.  
  19. CREATE FUNCTION [dbo].[GREATEST](@op1 REAL, @op2 REAL) RETURNS REAL AS
  20. BEGIN
  21. DECLARE @RESULT REAL
  22. SET @RESULT = CASE WHEN @op1 >= @op2 THEN @op1 ELSE @op2 END
  23. RETURN @RESULT
  24. END
  25. GO
  26.  
  27.  
  28. CREATE FUNCTION [dbo].[IF](@expr1 sql_variant, @expr2 sql_variant, @expr3 sql_variant) RETURNS sql_variant AS
  29. BEGIN
  30. DECLARE @RESULT sql_variant
  31. SET @RESULT = CASE WHEN CAST(@expr1 AS INT) != 0 THEN @expr2 ELSE @expr3 END
  32. RETURN @RESULT
  33. END
  34. GO
  35.  
  36.  
  37. CREATE FUNCTION [dbo].[LPAD](@str nvarchar(MAX), @len INT, @padstr nvarchar(MAX)) RETURNS nvarchar(4000) AS
  38. BEGIN
  39. RETURN LEFT(@str + replicate(@padstr,@len),@len);
  40. END
  41. GO
  42.  
  43.  
  44. CREATE FUNCTION [dbo].[MD5](@VALUE VARCHAR(255)) RETURNS VARCHAR(32) AS
  45. BEGIN
  46. RETURN SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', @VALUE)),3,32);
  47. END
  48. GO
  49.  
  50.  
  51. CREATE FUNCTION [dbo].[SUBSTRING](@op1 nvarchar(MAX), @op2 sql_variant, @op3 sql_variant) RETURNS nvarchar(MAX) AS
  52. BEGIN
  53. RETURN CAST(SUBSTRING(CAST(@op1 AS nvarchar(MAX)), CAST(@op2 AS INT), CAST(@op3 AS INT)) AS nvarchar(MAX))
  54. END
  55. GO
  56.  
  57.  
  58. CREATE FUNCTION [dbo].[SUBSTRING_INDEX](@string VARCHAR(8000), @delimiter CHAR(1), @COUNT INT) RETURNS VARCHAR(8000) AS
  59. BEGIN
  60. DECLARE @RESULT VARCHAR(8000)
  61. DECLARE @END INT
  62. DECLARE @part INT
  63. SET @END = 0
  64. SET @part = 0
  65. IF (@COUNT = 0)
  66. BEGIN
  67. SET @RESULT = ''
  68. END
  69. ELSE
  70. BEGIN
  71. IF (@COUNT < 0)
  72. BEGIN
  73. SET @string = REVERSE(@string)
  74. END
  75. WHILE (@part < ABS(@COUNT))
  76. BEGIN
  77. SET @END = CHARINDEX(@delimiter, @string, @END + 1)
  78. IF (@END = 0)
  79. BEGIN
  80. SET @END = LEN(@string) + 1
  81. BREAK
  82. END
  83. SET @part = @part + 1
  84. END
  85. SET @RESULT = SUBSTRING(@string, 1, @END - 1)
  86. IF (@COUNT < 0)
  87. BEGIN
  88. SET @RESULT = REVERSE(@RESULT)
  89. END
  90. END
  91. RETURN @RESULT
  92. END
  93. GO