String functions

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)

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

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 )

Expression is a character, binary, text, ntext, or image expression.

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 :

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 :

SELECT STR (123.4567, 9, 3); 

GO

---------

  123.457

SELECT STR (123.4567, 8, 1); 

GO

--------
   123.5