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’
