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
)
Rank(
@String,
@Length
)
|
Split
the string and returns data in tabular format.
|
SELECT *
FROM dbo.SplitDelimiter('a,b,c', ',')
Output:
|
||||
|
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