String
Functions are the part of SQL server System functions, They can be found under
the programmability folder -->Functions--> System Functions --> String
Functions
List of
String functions
ASCII()
ASCII (
character_expression)
character_expression - Is an expression of the type char or varchar
Returns the ASCII code value of the leftmost character of a character
expression.
Is an expression of the type char or varchar
Return type - int
Example:
SELECT ASCII('A') AS A, ASCII('B') AS B,ASCII('a') AS a, ASCII('b') AS b, ASCII(1) AS [1], ASCII(2) AS [2],ASCII('Rain') as Rain,ASCII('Shine') as Shine
A B a b 1 2 Rain Shine
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
65 66 97 98 49 50 82 83
(1 row affected)
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
65 66 97 98 49 50 82 83
(1 row affected)
CHAR()
CHAR ( integer_expression )
Is an integer from 0 through 255. NULL is returned if the integer
expression is not in this range.
Return type - char(1)
SELECT CHAR(ASCII('N')) AS N ,ASCII('N') AS [ASCII-N]
N ASCII-N
---- -----------
N 78
---- -----------
N 78
Char function can be used to insert control characters into character
strings
Tab - char(9), Line feed-char(10) Carriage return- char(13)
Example
select 'hello'+char(9)+'world'
hello world
select 'hello'+char(13)+char(10)+'world'
hello
world
Substring()
Substring is a part of a string.
SUBSTRING ( expression ,start , length )
Start is an Integer that specifies the start
of the substring.
Length is an Integer that
specifies how many characters of the expression will
be returned
Return
type
Specified
expression
|
Return
type
|
char/varchar/text
|
varchar
|
nchar/nvarchar/ntext
|
nvarchar
|
binary/varbinary/image
|
varbinary
|
Example:
select substring('sql server Rain or shine',5,6)
server
len()
finds the length of the given string.
Len(string)
String – length of the string that need to be found.
Example:
select len('hello world')
11
Charindex()
CHARINDEX functions returns the location of a
substring in a string.
Charindex(substring,string,[start_position])
Substring
- a part of string which
you want to find.
String - string to search within
[start_position]-optional, position
in string where
the search will start. The first position is 1.
Return
type : int or bigint
Example
select charindex('server','sql server Rain or shine') 5
select charindex(substring('sql server Rain or shine',5,6),'sql server Rain or shine') 5
select charindex('rain','sql server Rain or shine',5) -12
left()
LEFT function allows you to extract a
substring from a string, starting from the left-most character.
Syntax:
left(string,no of characters)
string
The string that you wish to extract
from.
number_of_characters
The number of characters that you
wish to extract starting from the left-most character.
Example:
select Left('helloworld',5)
hello
right()
Right function allows you to extract a
substring from a string, starting from the left-most character.
Syntax:
right(string,no of characters)
string
The string that you wish to extract
from.
number_of_characters
The number of characters that you
wish to extract starting from the right-most character.
Example:
select Left('helloworld',5)
world
LOWER()
LOWER function converts all letters in the specified string to lowercase.
If there are characters in the string that are not letters, they are unaffected
by this function
Lower(string)
String-The string to convert to lowercase
Example
select lower('HELLOWORLD')
helloworld
UPPER()
UPPER function converts all letters in the specified string to lowercase. If
there are characters in the string that are not letters, they are unaffected by
this function
Upper
(string)
String-The string to convert to uppercase
Example
select upper('helloworld')
HELLOWORLD
LTRIM()
LTRIM
function removes the space characters of string from the left hand side
LTRIM(string)
String
: string that to be trimmed
select Ltrim(' Hello')
Hello
select Len(' Hello')-7
select Len(Ltrim(' Hello'))-5
RTRIM()
RTRIM
function removes the space characters of string from the right hand side
RTRIM(string)
String
: string that to be trimmed
select Rtrim('Hello ')
Hello
select Len(‘Hello ')-7
select Len(Rtrim('Hello '))-5
PATINDEX()
Returns
the starting position of the first occurrence of a pattern in a specified
expression, or zeros if the pattern is not found, on all valid text and
character data types.
Patindex(pattern,expression)
Pattern
Is a
character expression that contains the sequence to be found. Wildcard
characters can be used; however, the % character must come before and
follow pattern (except
when you search for first or last characters). pattern is an expression of the
character string data type category. pattern is
limited to 8000 characters
Expression
is where we find the pattern
If
expression or pattern is null then the
patindex returns null
Return
type : bigint
Example
select patindex('%rain%','sql server rain or shine') 12
TRIM()
Function available from SQL server 2017 which works as a combination of Ltrim() and Rtrim() with little advancement it also has an option to remove unwanted characters
Synatx TRIM ( [ characters FROM ] string )
Example:
1) SELECT TRIM( ' Sandeep ') AS Result;
Result
----------------
Sandeep
2) In this example you could provide and list of character you would like to ingnore while trimming the result
SELECT TRIM( '.,! #' FROM '#$ Sandeep !.') AS Result;
Result
--------------------
$ Sandeep
Concat_Ws()
Concat_Ws is display as ConcatWs in ssms is new function added in SQL server 2017, which works as concatenate() in excel with an additional feature of Concatenating arguments with supplied delimiter
Synatx: CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )
Example :
Concat_Ws is display as ConcatWs in ssms is new function added in SQL server 2017, which works as concatenate() in excel with an additional feature of Concatenating arguments with supplied delimiter
Synatx: CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )
Example :
1) Print Concat_WS( ' - ', 'Test1', 'Test2', 'Test3')
Test1 - Test2 - Test3
2) SELECT Top 2 Concat_WS( ' - ', BusinessEntityID, FirstName, LastName) AS PersonInfo
FROM person.person;
PersonInfo
----------------------------------------------------------------------------------------------------------------------
285 - Syed - Abbas
293 - Catherine - Abel
STR()
STR function converts numeric data to character data
Synatx STR ( float_expression [ , length [ , decimal ] ] )
Float_expression is the numeric information we supply
Length is what output length you want to be
decimal is no of decimals you want in the output
Example :
STR function converts numeric data to character data
Synatx STR ( float_expression [ , length [ , decimal ] ] )
Float_expression is the numeric information we supply
Length is what output length you want to be
decimal is no of decimals you want in the output
Example :
SELECT STR (123.4567, 9, 3);
GO
---------
123.457
SELECT STR (123.4567, 8, 1);
GO
--------
123.5
123.5
