EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial T-SQL String Functions
 

T-SQL String Functions

Priya Pedamkar
Article byPriya Pedamkar

T-SQL String Functions

Introduction of T-SQL String Functions

Almost all web/desktop application needs a persistence layer to store the necessary information. Most of the application leverages database management system which stores data in a structured manner (unlike Mongo DB) like MySQL, SQL Server, Sybase, Postgres, and other database systems developed by tech giants like Oracle, IBM, and Microsoft. All these RDBMS products utilize one common language, which is SQL (Structured Query Language). Later these vendors developed their database language by extending the capability of the SQL language. T-SQL String Functions is one such language developed by Microsoft.

 

 

Let’s get started

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Now that you know what T-SQL is, let’s get started. Let’s look at some useful built-in SQL functions in T-SQL.

T-SQL String Functions

Let us discuss the T-SQL String Functions below,

1. ASCII

It returns the ASCII code of the leftmost character. The return type is Integer.

Syntax:

ASCII ( character expression )

T SQL String Functions - ASCII TSQL

Result:

ASCII TSQL RESULT2. CHAR

This function does the opposite of the ASCII function, i.e., it converts the ASCII value to the character value.

Syntax:

CHAR (integer expression)

T SQL String Functions - CHAR

Result:

CHAR TSQL RESULT

Tip: CHAR(13) can be used in the query for introducing a new line character.

3. CHARINDEX

This T-SQL string function returns the position of a substring in a string.

Syntax:

CHARINDEX(expressionToFind, expressionToSearch [, start_location])

Arguments:

  • expressionToFind: required parameter which denotes the substring to search for
  • expressionToSearch: required parameter, string in which the substring is to be searched
  • start_location: optional parameter defines the position from which the search will start; if not defined, then it will start from the beginning of the string by default.

T SQL String Functions - CHARINDEX

Result:

CHARINDEX TSQL RESULT

4. CONCAT

This function appends two or more string values in an end-to-end manner and returns the concatenated string.

Syntax:

CONCAT ( string_value1, string_value2 [, string_valueN ] )

Arguments:

The concat function requires at least two string arguments that can extend up to 254 string arguments.

Return Type

The return type is a concatenated string of the input strings.

T SQL String Functions - CONCAT TSQL

Result:

CONCAT TSQL RESULT

5. CONCAT_WS

This T-SQL string function appends two or more string values in an end-to-end manner and separates them with the delimiter defined in the first parameter.

Syntax:

CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )

Arguments:

  • Separator: it could be an expression of any type char, nchar, nvarchar, or varchar

Return Type

The return type concatenated the string with a delimiter in between the strings.

T SQL String Functions - CONCAT_WS

Result:

CONCAT_WS TSQL RESULT

Note: In the case of Null values concat_ws does not append delimiters and returns an empty value.

T SQL String Functions - CONCAT_WS NULL VALUE

Result:

CONCAT_WS NULL VALUE RESULT

6. Difference

This function returns an integer value measuring the SOUNDEX() values of the two character expressions.

Syntax:

DIFFERENCE ( character_expression , character_expression )

Arguments:

  • character_expression: it could be an alphanumeric expression of a character data; it can be a constant, variable, or column.

Return Type

Integer

Note: difference compares two different SOUNDEX values and returns an integer value; this value indicates the SOUNDEX values match in a range of 0 to 4, where 0 signifies no match or zero similarity and 4 signifies identically matching strings.

Difference TSQL

Result:

Difference TSQL RESULT

7. FORMAT

This T-SQL string function returns the formatted string value in the specified format and optional culture. Use this function for locale formatting of date, time, and number value.

Syntax:

FORMAT ( value, format [, culture ] )

Arguments:

  • Value: required, the value to be formatted.
  • Format: required, defines the format pattern
  • Culture: optional, defines a local culture

T SQL String Functions - FORMAT

Result:

FORMAT TSQL RESULT

8. LEFT

This function returns the specified number of characters starting from the left.

Syntax:

LEFT ( character_expression , integer_expression )

Arguments:

  • character_expression: required, signifies the character string from which characters will be extracted
  • integer_expression: required, signifies the number of characters to extract.

T SQL String Functions - LEFT

Result:

LEFT TSQL RESULT

9. LEN

This T-SQL string function returns the number of characters of a specified string.

Syntax:

LEN ( string_expression )

Arguments:

  • string_expression: the string variable whose length is to be determined. It can be a constant, string variable, or column.

T SQL String Functions - LEN TSQL

Result:

LEN TSQL RESULT

10. LOWER

This function returns the string expression after converting the uppercase characters to lowercase.

Syntax:

LOWER ( character_expression )

Arguments:

  • character_expression: signifies the string that needs to be converted in lowercase.

T SQL String Functions - LOWER

Result:

 LOWER TSQL RESULT

11. LTRIM

This T-SQL string function returns the character expression after removing the leading spaces.

Syntax:

LTRIM ( character_expression )

Arguments:

  • character_expression: required field, the string variable from which leading spaces are to be removed.

T SQL String Functions - LTRIM

Result:

LTRIM TSQL RESULT

12. NCHAR

This function returns the Unicode character of the specified integer code defined by the Unicode standard.

Syntax:

NCHAR ( integer_expression )

Arguments:

  • integer_expression: It can be a positive integer value from 0 to 6535; if a value greater than this is specified, Null is returned.

T SQL String Functions - NCHAR

Result:

NCHAR TSQL RESULT

13. PATINDEX

This T-SQL string function returns the index of the first occurrence of the specified pattern in a string expression.

Syntax:

PATINDEX ( '%pattern%' , expression )

Arguments:

  • Pattern: requires a field; this is a character expression that contains a sequence to be found in the string expression. Wildcard characters can also be used in the pattern; the maximum limit is 8000 characters. It must be surrounded by %.
  • Expression: it can be a column in which the specified pattern is to be searched.

T SQL String Functions - PATINDEX

Result:

PATINDEX TSQL RESULT

14. REPLACE

This function replaces all occurrences of a specified string value with another value.

Syntax:

REPLACE ( string_expression , string_pattern , string_replacement )

Arguments:

  • string_expression: required; this is the string expression to be searched.
  • string_pattern: required; this is the string pattern in the String expression.
  • string_replacement: required, this is the string that replaces the string pattern.

REPLACE TSQL

Result:

REPLACE TSQL RESULT

15. SUBSTRING

This function extracts a specified character from a string.

Syntax:

SUBSTRING ( expression ,start , length )

Arguments:

  • Expression: required, defines the string expression from which a part would be extracted.
  • Start: required, defines the starting index from which the string will be extracted.
  • Length: required, defines the length up to which the string must be extracted.

SUBSTRING TSQL

Result:

SUBSTRING TSQL RESULT

16. Reverse

This function reverses the string expression character by character and returns the reversed string.

Syntax:

REVERSE ( string_expression )

Arguments:

  • String_expression: requires and defines the string which is to be reversed.

Reverse TSQL

Result:

Reverse TSQL RESULT

Conclusion

This tutorial taught us the commonly used and extremely useful string functions. These string functions are widely used while writing complex stored procedures and triggers. You can also create custom string functions using the predefined functions. That will be covered in the later part of the tutorial.

Recommended Articles

We hope that this EDUCBA information on “T-SQL String Functions” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. T-SQL Commands
  2. Introduction to MySQL
  3. SQL Commands
  4. Uses of SQL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW