Blog

Select ASCII ('word') 

CHAR()

The character will come as output for the given Ascii code or integer.

Example

The following query will give the character for a given integer.

Select CHAR(97)

NCHAR()

Unicode character will come as output for a given integer.

Example

The following query will give the Unicode character for a given integer.

Select NCHAR(300)

CHARINDEX()

The starting position for a given search expression will come as output in a given string expression.

Example

The following query will give the starting position of the ‘G’ character for the given string expression ‘KING’.

Select CHARINDEX('G', 'KING')

LEFT()

Left part of the given string till the specified number of characters will come as output for a given string.

Example

The following query will give the ‘WORL’ string as mentioned 4 number of characters for given string ‘WORLD’.

Select LEFT('WORLD', 4)

RIGHT()

The right part of the given string till the specified number of characters will come as output for a given string.

Example

The following query will give the ‘DIA’ string as mentioned 3 number of characters for the given string ‘INDIA’.

Select RIGHT('INDIA', 3)

SUBSTRING()

Part of a string based on the start position value and length value will come as output for a given string.

Example

The following queries will give the ‘WOR’, ‘DIA’, ‘ING’ strings as we mentioned (1,3), (3,3) and (2,3) as start and length values respectively for given strings ‘WORLD’, ‘INDIA’ and ‘KING’.

Select SUBSTRING ('WORLD', 1,3) 

Select SUBSTRING ('INDIA', 3,3) 

Select SUBSTRING ('KING', 2,3)

LEN()

A number of characters will come as output for a given string expression.

Example

The following query will give the 5 for the ‘HELLO’ string expression.

Select LEN('HELLO') 

LOWER()

The lowercase string will come as output for a given string data.

Example

The following query will give the ‘SQL server for the ‘SQLServer’ character data.

Select LOWER('SQLServer') 

UPPER()

The uppercase string will come as output for a given string data.

Example

The following query will give the ‘SQLSERVER’ for the ‘SqlServer’ character data.

Select UPPER('SqlServer')

LTRIM()

String expression will come as output for a given string data after removing leading blanks.

Example

The following query will give the ‘WORLD’ for the ‘   WORLD’ character data.

Select LTRIM('   WORLD')

RTRIM()

String expression will come as output for a given string data after removing trailing blanks.

Example

The following query will give the ‘INDIA’ for the ‘INDIA   ‘ character data.

Select RTRIM('INDIA   ') 

REPLACE()

String expression will come as output for a given string data after replacing all occurrences of specified characters with specified characters.

Example

The following query will give the ‘KNDKA’ string for the ‘INDIA’ string data.

Select REPLACE('INDIA', 'I', 'K')

REPLICATE()

Repeat string expression will come as output for a given string data with a specified number of times.

Example

The following query will give the ‘WORLDWORLD’ string for the ‘WORLD’ string data.

Select REPLICATE('WORLD', 2)

REVERSE()

Reverse string expression will come as output for a given string data.

Example

The following query will give the ‘DLROW’ string for the ‘WORLD’ string data.

Select REVERSE('WORLD')

SOUNDEX()

Returns four-character (SOUNDEX) code to evaluate the similarity of two given strings.

Example

The following query will give the ‘S530’ for the ‘Smith’, ‘Smyth’ strings.

Select SOUNDEX('Smith'), SOUNDEX('Smyth')

DIFFERENCE()

The integer value will come as the output of the given two expressions.

Example

The following query will give the 4 for the ‘Smith’, ‘Smyth’ expressions.

Select Difference('Smith','Smyth') 

Note − If the output value is 0 it indicates weak or no similarity between giving 2 expressions.

SPACE()

The string will come as an output with the specified number of spaces.

Example

The following query will give the ‘I LOVE INDIA’.

Select 'I'+space(1)+'LOVE'+space(1)+'INDIA'

STUFF()

String expression will come as output for a given string data after replacing from starting character till the specified length with specified character.

Example

The following query will give the ‘AIJKFGH’ string for the ‘ABCDEFGH’ string data as per the given starting character and length as 2 and 4 respectively and ‘IJK’ as specified target string.

Select STUFF('ABCDEFGH', 2,4,'IJK') 

STR()

Character data will come as an output for the given numeric data.

Example

The following query will give the 187.37 for the given 187.369 based on specified length as 6 and decimal as 2.

Select STR(187.369,6,2) 

UNICODE()

The integer value will come as output for the first character of the given expression.

Example

The following query will give the 82 for the ‘RAMA’ expression.

Select UNICODE('RAMA') 

QUOTENAME()

The given string will come as output with the specified delimiter.

Example

The following query will give the “RAMA” for the given ‘RAMA’ string as we specified double quote as a delimiter.

Select QUOTENAME('RAMA','"') 

PATINDEX()

Starting position of the first occurrence from the given expression as we specified ‘I’ position is required.

Example

The following query will give the 1 for the ‘INDIA’.

Select PATINDEX('I%','INDIA') 

FORMAT()

The given expression will come as output with the specified format.

Example

The following query will give the ‘ Monday, November 16, 2015’ for the getdate function as per specified format with ‘D’ refers weekday name.

SELECT FORMAT ( getdate(), 'D') 

CONCAT()

The single string will come as output after concatenating the given parameter values.

Example

The following query will give the ‘A, B, C’ for the given parameters.

Select CONCAT('A',',','B',',','C')