Nov 6, 2015

SQL Server utility functions big list commonly used

C#, VB.net, Java, MySql, Oracle equivalent in functions in SQL Server

Function
Description
Example
  @StrA
 ,@StrB
 ,@IndexA
 ,@IndexB
 ,@Length
 ,@IgnoreCase
)          
Compare two sub strings and returns a number zero if same sort position, a positive number if @StrA has higher sort position otherwise a negative number.
SELECT dbo.Compare(
      'a',
      'A',
      DEFAULT,
      DEFAULT,
      DEFAULT,
      DEFAULT)

Output: 32
Concat(
 Str1,
 Str2,
 ...
)
Concatenate n umber of string. If string is NULL then make it empty string.
DECLARE @Str1 AS VARCHAR(100)
DECLARE @Str2 AS VARCHAR(100)

SET @Str1 = 'Exact'
SET @Str2 = 'Help'

SELECT CONCAT (@Str1, @Str2)

--OR
SELECT ISNULL(@Str1, '') + ISNULL(@Str2, '')

Output: ExactHelp
Contains(
  @Str
 ,@Val
)
Returns a boolean indicating whether a specified substring occurs within this string.
DECLARE @Str VARCHAR(MAX)
DECLARE @Val AS VARCHAR(15)

SET @Str = 'Learning SQL from exacthelp.com'
SET @Val = 'SQL'

SELECT CASE
            WHEN @Str LIKE '%' + @Val + '%'
                  THEN 1
            ELSE 0
            END

--OR
SELECT CASE
            WHEN CHARINDEX(@Val, @Str) > 0
                  THEN 1
            ELSE 0
            END

Output: 1
CopyTo(
  @StrSource
 ,@StrDestination
 ,@IndexSource
 ,@IndexDestination
 ,@Count
)
Copies a specified number of characters from a specified position in this string to a specified position in a other string.
DECLARE @StrSource VARCHAR(MAX) = 'exact_sql.com'
DECLARE @StrDestination VARCHAR(MAX) = 'help'
DECLARE @IndexSource INT = 6
DECLARE @IndexDestination INT = 1
DECLARE @Count INT = 4

SELECT STUFF(@StrSource, @IndexSource, @Count, RIGHT(@StrDestination, 1 + LEN(@StrDestination) - @IndexDestination))

Output: exacthelp.com
GetHashCode(
 @Str
)
Get has code of specified string.
DECLARE @Str VARCHAR(4000)
SET @Str = 'Exact Help'

SELECT HASHBYTES('SHA1', @Str)

Output: 0x10608CEF55C44F55E8675
11B97C6411E948FA1A3
EndsWith(
  @Str
 ,@Value
)
Check string end with of given string
DECLARE @Str AS VARCHAR(MAX)
DECLARE @Value AS VARCHAR(15)

SET @Str = 'www.exacthelp.com'
SET @Value = '.com'

SELECT CASE
            WHEN @Str LIKE '%' + @Value
                  THEN 1
            ELSE 0
            END

Output: 1
 @Chars,
 @Delimiter,
 @String
)
Return index of the first occurrence in the string of any character in a specified string characters.
SELECT [dbo].[IndexOfAny]('A,B,C', ',', 'xxxBxxxAxxxCxxx')

Output: 4
Insert(
 @Str,
 @StartIndex,
 @Value
)
Returns a new string in which a specified string is inserted at a specified index position.
DECLARE @Str AS VARCHAR(MAX)
DECLARE @StartIndex AS INT
DECLARE @Value AS VARCHAR(100)

SET @Str = 'www.exact.com'
SET @StartIndex = 9
SET @value = 'help'

SELECT LEFT(@Str, @StartIndex) + @Value + RIGHT(@Str, LEN(@Str) - @StartIndex)


Output: www.exacthelp.com
 @chars,
 @String
)
Returns index position of the last occurrence of a specified character within the string.
SELECT dbo.LastIndexOf('A','AoneATwoAThree')
Output: 9
Remove(
 @Str
)
Returns a new string in which a specified number of characters in the current instance beginning at a specified position have been deleted.
DECLARE @Str AS VARCHAR(MAX)
DECLARE @StartIndex AS INT
DECLARE @Count AS INT

SET @Str = 'www.exacthelpblogspot.com'
SET @StartIndex = 14
SET @Count = 8

SELECT STUFF(@Str, @StartIndex, @Count, '')

Output: www.exacthelp.com


PadLeft(
  @Str
 ,@TotalWidth
 ,@PadChar
)

Returns a new string that left-aligns the characters in this string by padding them on the right with a specified Unicode character, for a specified total length.
DECLARE @Str AS VARCHAR(MAX)
DECLARE @TotalWidth AS INT
DECLARE @PadChar CHAR(1)

SET @Str = 'www.exacthelp.com'
SET @TotalWidth = 20
SET @Pad = '*'

SELECT ISNULL(REPLICATE(@PadChar, @TotalWidth - LEN(@Str)), '') + @Str

Output: ***www.exacthelp.com
PadRight(
  @Str
 ,@TotalWidth
 ,@PadChar
)

Returns a new string that left-aligns the characters in this string by padding them on the right with a specified Unicode character, for a specified total length.
DECLARE @Str AS VARCHAR(MAX)
DECLARE @TotalWidth AS INT
DECLARE @PadChar CHAR(1)

SET @Str = 'www.exacthelp.com'
SET @TotalWidth = 20
SET @Pad = '*'

SELECT @Str + ISNULL(REPLICATE(@PadChar, @TotalWidth - LEN(@Str)), '')

Output: www.exacthelp.com***
 @String,
 @Delimiter
)

 @String,
 @Delimiter
)

 @String,
 @Length
)

 @String,
 @Length
)

Split the string and returns data in tabular format.
SELECT *
FROM dbo.SplitDelimiter('a,b,c'',')

Output:

Item
a
b
c
StartWith(
  @Str
 ,@Value
)
Determines whether the beginning of this string instance matches the specified string.
DECLARE @Str AS VARCHAR(MAX)
DECLARE @Value AS VARCHAR(15)

SET @Str = 'www.exacthelp.com'
SET @Value = 'http'

SELECT CASE
            WHEN @Str LIKE @Value + '%'
                  THEN 1
            ELSE 0
            END

Output: 0
Trim(
 @Str
)
Removes all leading and trailing white-space characters from the current string
SELECT LTRIM(RTRIM('   EXACT HELP   '))

Output: EXACT HELP

No comments:

Post a Comment