EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Database Management Tutorial T-SQL String Functions
Secondary Sidebar
Database Management Tutorial
  • TSQL Basic
    • TSQL
    • What is T-SQL
    • T-SQL Commands
    • T-SQL String Functions
    • TSQL Interview Questions
  • DataBase Management
    • Text Data Mining
    • Roles of Database Management System in Industry
    • SQL Server Database Management Tools
    • Databricks CLI
    • Database administrator skills
    • Database Management Systems Advantages
    • Database Testing Interview Questions
    • Netezza Database
    • Data Administrator
    • Database Administrator
    • Data manipulation
    • Database Management Software
    • DataStage
    • Types of Database Models
    • Types of Database
    • Hierarchical Database Model
    • Relational Database
    • Relational Database Advantages
    • Operational Database
    • What is RDBMS?
    • Data Masking Tools
    • Database Security
    • Data Replication
    • Bitmap Indexing
    • Second Normal Form
    • Third Normal Form
    • Fourth Normal Form
    • Data Definition Language
    • Data Manipulation Language
    • Data Control Language
    • Transaction Control Language
    • Dataset Normalization
    • jdbc connection
    • Conceptual Data Model
    • Entity-Relationship Model
    • Relational Database Model
    • Sequential File Organization
    • Teradata Create Table
    • Teradata Database
    • Centralized Database
    • Data Storage in Database
    • Thomas write Rule
    • DBA Interview Questions
    • What is JDBC?
    • jdbc hive
    • Apriori Algorithm
    • JDBC Architecture
    • JDBC Interview Questions
    • Datastage Interview Questions
    • Wildcard Characters
    • Distributed Database System
    • Multidimensional Database

Related Courses

SQL Certification Course

PL/SQL Certification Course

Oracle Certification Course

T-SQL String Functions

By Priya PedamkarPriya Pedamkar

T-SQL String Functions

Introduction of T-SQL String Functions

Almost all the 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 own 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

Now that you have got a gist of what T-SQL, let’s get started. Let’s have a look at some of the useful built-in SQL functions available in the T-SQL.

T-SQL String Functions

Let us discuss the T-SQL String Functions below,

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

1. ASCII

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

Syntax:

ASCII ( character expression )

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,354 ratings)

T SQL String Functions - ASCII TSQL

Result:

ASCII TSQL RESULT2. CHAR

This function does the exact opposite of the ASCII function, i.e. it converts 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 newline 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 by default it will start from the beginning of the string.

T SQL String Functions - CHARINDEX

Result:

CHARINDEX TSQL RESULT

4. CONCAT

This function appends the 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 strings 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 the 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 a column.

Return Type

Integer

Note: difference compares two different SOUNDEX values and returns an integer value, this value is an indication of 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 extract
  • 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 lower case.

Syntax:

LOWER ( character_expression )

Arguments:

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

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 then 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 specified 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 to be found 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 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, defines the string which is to be reversed.

Reverse TSQL

Result:

Reverse TSQL RESULT

Conclusion

In this tutorial, we have learned the commonly used and extremely useful string functions. These string functions are commonly 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

This has been a guide to T-SQL String Functions. Here we have discussed the working of T-SQL String Functions with the help of syntax and result. You may also look at the following articles to learn more:

  1. T-SQL Commands
  2. Introduction to MySQL
  3. SQL Commands
  4. Uses of SQL
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

PL SQL Training (4 Courses, 2+ Projects)4.9
Oracle Training (14 Courses, 8+ Projects)4.8
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more