Home > SQL Server, T-SQL > SQL SERVER – String functions at a glance – T-SQL @SQLSERVER

SQL SERVER – String functions at a glance – T-SQL @SQLSERVER


Built in functions are the most valuable piece of code that reduces the lot of time for developers. Every programming language has some special feature and purpose. T-SQL helps SQL Developer to write some code to process their data stored in SQL tables.

This article will give you some details about string function up to SQL Server 2008 R2. Please go to this blog post to learn about New Functions in SQL SERVER 2012.

Functions:

1) ASCII(Character) – Returns ASCII numeric value for a given character

Select ASCII('A'), ASCII('a')
Result:
65	97

2) CHAR(integer range 0 to 255) – Returns ASCII character for the given integer

Select CHAR(65), CHAR(97)
Result:
A	a

3) SPACE(positive integer value) – Returns space ASCII characters as given in the parameter.

Select ‘A’+SPACE(4)+’A’
Result
A A

4) STR(numeric or float value, [length, decimal ]) – Converts numbers to characters and returns varchar (string).

SELECT STR(1234.567, 6,1)
Result:
1234.6

5) LEFT(string value, length) – Returns left portion of the given string with specified length

SELECT LEFT(‘Microsoft’,5)
Result:
Micro

6) RIGHT(string value, length) – Returns right porting of the given string with specified length

SELECT RIGHT(‘Microsoft’,4)
Result:
soft

7) SUBSTRING ( string, start , length ) – Returns a portion of the given string as specified length from the start point.

SELECT SUBSTRING(‘Microsoft SQL Server’,11,3)
Result:
SQL

8 ) LEN(string) – Returns length of the string in numeric value

SELECT LEN(‘Microsoft SQL Server’)
Result:
20

9) REVERSE (string) – Returns given string in reverse

SELECT REVERSE(‘Microsoft SQL Server’)
Result:
revreS LQS tfosorciM

10) UPPER(string) – Returns upper case character of all alphabet in the given string

SELECT UPPER(‘sql server is number 1’)
Result:
SQL SERVER IS NUMBER 1

11) LOWER(String) – Returns lower case character of all alphabet in the given string

SELECT LOWER(‘AYYAPPAN THANGARAJ’)
Result:
ayyappan thangaraj

12) LTRIM(string) – Removes blank space character from the left side of the given string.

SELECT LTRIM(‘  SQLServerRider  )’)
Result:
SQLServerRider  )

13) RTRIM(string) – Removes blank space character from the right side of the given string.

SELECT RTRIM(‘(  SQLServerRider  ‘)
Result:
(  SQLServerRider

14) REPLICATE ( character or string , integer ) – Duplicates given character(s) to n times as specified in the second parameter.

SELECT REPLICATE(‘Bravo! ‘,2)
Result:
Bravo! Bravo!

15) REPLACE(source_string, search_string, replace_string) – Returns a string after replacing the replace string if search string is found in source string.

SELECT REPLACE(‘SQL Server’, ‘SQL Server’, ‘Microsoft SQL Server’)
Result:
Microsoft SQL Server

These are the commonly used string function in SQL development.  there are some new function added in SQL 2012. Please read this article for more details SQL Server 2012 functions.

We do have these function in SQL Server Integration services. You can use Derived Column transformation to use functions for data manipulation.

Thanks for reading.

Advertisements
  1. November 2, 2011 at 1:47 pm

    Nice list of SQL Server String functions! I also like your article on the upcoming SQL Server 2012 (Denali) functions.

  2. November 8, 2011 at 9:02 am

    Very good information !!

  1. October 23, 2013 at 10:48 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: