Sql Server material 11

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 3^rd and 4^th 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

<<< Previous                                                                                                                          Next >>>