Home » T-SQL String functions

T-SQL String functions

by Online Tutorials Library

T-SQL String Function

In T-SQL, String function is applied on string value and returns the numeric data or the string value.

Type of string function with the help of an example is given below:

  • ASCII ()

ASCII code value comes as the output of character expression.

Example

The query will give the ASCII value of the character.

output:

068069076072073  
  • CHAR ()

The character will come like an output for the ASCII code or integer.

Example

The below query generates the character of the integer.

NCHAR ()

Using NCHAR (), Unicode character of any integer comes in the output that we have used.

Example

The query returns the Unicode character of the integer.

CHARINDEX ()

The starting position for any search expression will come as an output in the string expression.

Example

The following query will generate the starting position of ‘A‘ character in the string ‘tutoraspire.’

LEFT()

LEFT () is used to return the left part of the string till the specified number of characters of the given string.

Example

The query returns the string as mentioned four numbers of characters for the string ‘INDIA’.

output:

INDI  

RIGHT ()

It returns the right part of any string till the specified number of characters as output for the input string.

Example

The query will give the ‘DIA’ string as mentioned three characters for input string ‘INDIA.’

output:

DIA  

SUBSTRING()

SUBSTRING is the part of a string that is based on the start position, and the length value will come like the output of a string.

Example

The given command will give ‘FORM,’ ‘DIA,’ ‘EEN’ strings which we have mentioned (1,3), (3,3), and (2,3) respectively, and then the length values as strings’ world. For ‘,’ India ‘and’ QUEEN’.

LEN ()

LEN() is the number of characters that come as the output of the string expression.

Example

The query returns 4 for the ‘JAVA’ string.

LOWER()

The Lowercase string comes as an output of the string data.

Example

The query gives the ‘sqlserver’ for the ‘SQL‘ character data.

UPPER()

Uppercase string is returned as the output of the string data.

Example

The query gives the ‘tutoraspire‘ for the ‘tutoraspire‘ character data. It changes the string in uppercase.

LTRIM ()

The String expression will return as an output for string data after removing the leading blanks in the LTRIM ().

Example:

The query gives the ‘TUTORIAL’ for the ‘ TUTORIAL’ character data.

RTRIM ()

RTRIM () in String expression returns the output for the given string data after removing the blanks.

Example

The query gives the ‘ASIA’ for the ‘ASIA ‘ character data.

REPLACE ()

The string expression for the given string data comes as output after replacing all the frequencies of the character with another one.

Example

The query returns the ‘KNDKA’ string for the ‘ASIA’ string data.

REPLICATE()

REPLICATE() is used to repeat string expression, which gives the output of string data for multiple times.

Example

REVERSE ()

Reverse string expression comes as the output for the string operation.

Example

The query gives the ‘LAPTOP’ string for the ‘POTPAL’ string data. REVERSE() will reverse our string.

SOUNDEX ()

SOUNDEX () returns four-character (SOUNDEX) code to evaluate one or more characters.

Example:

The query gives the ‘S530’ for the ‘Twist’ and ‘Twyst’ strings.

DIFFERENCE ()

The Integer value comes as an output of two input expressions.

Example

The following query gives 4 for the ‘Michal,’ ‘Micaal’ expressions.

Note – If the output is 0, it indicates the weak similarity between the expressions.

SPACE ()

SPACE String is returned as output with the number of spaces.

Example

The query will give the ‘tutoraspire HAS CONTENTS.’

STUFF ()

The expression of a string returns the output of any string data after the original character is changed to the length specified with style.

Examples

The query will return the ‘MNOPQR’ string according to the first character, and length is given for the ‘ABCDEFGH’ string data as 2 and 4 and ‘IJK’ as the targeted string.

STR ()

Character data is returned as the output of the numeric data.

Example

The query will give the 187.37 for the given 156.901 based on the length as eight and decimal as 5.

UNICODE ()

In UNICODE () character, integer value comes as the output of the first character of the given expression.

Example

The following query returns 56 for the ‘PHP’ expression.

QUOTENAME ()

The string would come as an output with the delimiter.

Example

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

PATINDEX ()

In PATINDEX, starting position of the first occurrence of the expression where we specified ‘N’ view is required.

Example

The following query will give 1 for ‘ASIA.’

FORMAT ()

In FORMAT(), given expression come as output with the changed format.

Example

The following query gives the ‘ Friday, DECEMBER 16, 2019’ for the getdate() function as per specified format with ‘D’ refers to the weekday name.

CONCAT ()

In CONCAT (), the single string has come as the output after concatenating the given parameter values.

Example

The below query gives the ‘X, Y, Z’ for the given parameters.


You may also like