SQL SERVER 2008 – FUNCTIONS In SQL SERVER 2008 – String Functions

These functions perform an operation on a string input value and return a string or numeric value.

ASCII(s): Returns the ASCII code value of the leftmost character of the expression.
ASCII(‘A’)     OUTPUT: 65
ASCII(‘BCD’)     OUTPUT: 66

CHAR(n): Converts the given ASCII code to a character.
CHAR(97)     OUTPUT: a

NCHAR(n): Returns the Unicode character with the specified integer code ranging between 0 to 65, 535, as defined by the Unicode standard.
CHAR(300)     OUTPUT: Ĭ

CHARINDEX(search exp, string exp [ , start_location ] ): Returns the starting position of the search exp in the string exp which can also be a column name.
CHARINDEX(‘O’, ‘HELLO WORLD’)  OUTPUT: 5
-In this case it returns 5 as output because it starts its search from the beginning of the string, we can change it by using the start location optional parameter.
CHARINDEX(‘O’, ‘HELLO WORLD’, 6)  OUTPUT: 8
-WAQ to get the details of employees whose name contains the character ‘M’ in it.
Sol: SELECT * FROM EMP WHERE CHARINDEX(‘M’, ENAME)>0

LEFT(s, n): Returns the left part of the string with the specified number of characters.
SELECT LEFT(‘HELLO’, 3)    OUTPUT: HEL
-WAQ to get the details of employees whose name contains the first 2 characters as ‘VE’.
Sol: SELECT * FROM EMP WHERE LEFT(ENAME, 2)=’VE’

RIGHT(s, n): Returns the right part of the string with the specified number of characters.
SELECT RIGHT(‘HELLO’, 3)   OUTPUT: LLO
-WAQ to get the details of employees whose name ends with characters ‘TT’.
Sol: SELECT * FROM EMP WHERE RIGHT(ENAME, 2)=’TT’

SUBSTRING(s, start, length): Returns a part of a string from string s starting from start position, where length is the no of chars to be picked.
SELECT SUBSTRING(‘HELLO’, 1, 3)  OUTPUT: HEL
SELECT SUBSTRING(‘HELLO’, 3, 3)  OUTPUT: LLO
SELECT SUBSTRING(‘HELLO’, 2, 3)  OUTPUT: ELL
-WAQ to get the details of employees whose names 3rd and 4th characters are ‘TI’.
Sol: SELECT * FROM EMP WHERE RIGHT(LEFT(ENAME, 4), 2)=’TI’
Sol: SELECT * FROM EMP WHERE SUBSTRING(ENAME, 3, 2)=’TI’

LEN(s): Returns the number of characters of the specified string expression, excluding trailing blanks.
SELECT LEN(‘HELLO’)    OUTPUT: 5
SELECT LEN(‘   HELLO’)    OUTPUT: 8
-WAQ to get the details of employees whose names was 5 characters in length
Sol: SELECT * FROM EMP WHERE LEN(ENAME)=5
SELECT LEN(‘HELLO   ‘)    OUTPUT: 5

LOWER(s): Returns a character expression after converting the given character data to lowercase.
SELECT LOWER(‘Hello’)    OUTPUT: hello

UPPER(s): Returns a character expression after converting the given character data to uppercase.
SELECT UPPER(‘Hello’)    OUTPUT: HELLO

LTRIM(s): Returns a character expression after it removes leading blanks.
SELECT LEN(LTRIM(‘   HELLO’))   OUTPUT: 5
SELECT ‘HELLO ‘ + LTRIM(‘ WORLD’)  OUTPUT: HELLO WORLD

RTRIM(s): Returns a character expression after it removes trailing blanks.
SELECT RTRIM(‘HELLO ‘) + ‘ WORLD’  OUTPUT: HELLO WORLD

REPLACE(s1, s2, s3): Replaces all occurrences of the s2 in s1 with s3.
SELECT REPLACE(‘HELLO’, ‘L’, ‘X’)  OUTPUT: HEXXO

REPLICATE(s, n): Repeats the expression ‘s’ for specified ‘n’ number of times.
SELECT REPLICATE(‘HEL’, 2)   OUTPUT: HELHEL

REVERSE(s): Returns the reverse of the given string ‘s’.
SELECT REVERSE(‘HELLO’)   OUTPUT: OLLEH

SOUNDEX(s): Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings. SOUNDEX converts an alphanumeric string to a four-character code to find similar-sounding words or names. The first character of the code is the first character of strings and the second through fourth characters of the code are numbers.
SELECT SOUNDEX (‘Smith’), SOUNDEX (‘Smyth’)
-Generally we use then when we perform comparison of words, which are sounded in the same way but have different spelling like color & colour. Suppose in a table the ename of a person is smith we will get the result even if the statement is written as following:
SELECT * FROM EMP WHERE SOUNDEX(ENAME)=SOUNDEX(‘SMYTH’)

DIFFERENCE(S1, S2): Returns an integer value that indicates the difference between the SOUNDEX values of two character expressions. The return value ranges from 0 through 4: 0 indicates weak or no similarity, and 4 indicates strong similarity or the same values.
SELECT SOUNDEX(‘SMITH’), SOUNDEX(‘SMYTH’),
DIFFERENCE(‘SMITH’,’SMYTH’)

SPACE(n): Returns a string with specified ‘n’ number of repeated spaces.
SELECT ‘HELLO’ + SPACE(1) + ‘WORLD’  OUTPUT: HELLO WORLD

STUFF(s, start, length, replace_str): Replaces specified length of characters from specified starting point with replace_str in the string ‘s’
SELECT STUFF(‘ABXXCDXX’, 3, 3, ‘YY’) OUTPUT: ABYYDXX

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

Advertisements

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