SqlTutorial-1 : Lesson-11 : Class-2
Microsoft SQL Server Training:
Free Online-Learning Classes (OLC) for T-SQL
Built-in Sql functions: String functions



Microsoft SQL Server Training Online Learning Classes Built in functions String functions

INDEX - (21 functions)
1. Type Conversion: 
  • ASCII to CHAR or vice versa:   
    ASCII() & CHAR() methods. 
  • UNICODE to CHAR or vice versa : 
    UNICODE() & NCHAR() methods. 
  • Tostring() : STR() 
2. Extracting Sub-string 
  • Corner-ended sub-strings:
    LEFT(), RIGHT(), 
  • Any positional sub-string
    SUBSTRING(),
  3. Find and replace 
  • To find:   CHARINDEX() 
  • Pattern matchig with wild-cards:
    PATINDEX()
  • To replace: REPLACE()  
4. String Comparision
  • Spelling-based comparision
    DIFFERENCE() 
  • Sond-based Comparision 
    SOUNDEX();
5. Case-Conversions
  • To lower case :LOWER() 
  • To upper case: UPPER()
6. Space Trimming and padding 
  • To trimming leading & trailing spaces: LTRIM() & RTRIM() 
  • To inserting blank space(s): SPACE()
  • To inserting text: STUFF()   
7. Determining length and Reverse 
  • Length of string: LEN() 
  • Reverse of string: REVERSE() 



1. Type-Conversion functions

  1. ASCII():

    Returns the ASCII integer value of the leftmost character of a string.
    Syntax
    ASCII ( character_expression );
    1. Converting Aphanumeric characters to ASCII-code.

      SELECT ASCII('H'), ASCII('e'), ASCII('l'), ASCII('l'), ASCII('o') => 72 101 108 108 111
    2. Converting Numeric letter to ASCII-Code....

      SELECT ASCII(CHAR(4)) => 4
    3. If multiple chars included then first char only converts

      SELECT ASCII('abcdef') => 97
  2. CHAR():

    Converts an integer ASCII code to a character.; reverse of ASCII().
    Syntax
    CHAR ( integer_expression )
    1. Finding character for ASCII-Code 97;

      SELECT CHAR(97)=> 'a'
    2. Display text with single quotation mark as 'Hello' or "Hello"

      SELECT CHAR(39) + 'Hello' + CHAR(39) -- use 34 for "
    3. Used to format the output. (Char(10)= ‘\n’, Char(9)=’\t’ , Char(‘\r’) ); e.g, Display record column-wise:

      Select "MyRecord"= sname + CHAR(10) + convert(char(3), rollNo))
      FROM student
      WHERE sname=’Abcd’
    4. Display record column-wise and terminate each record with line.

      SELECT "MyRecords"= sname + CHAR(10) + convert(char(3),rollNo) +
                 CHAR(10)+ convert(char(3),Total) +
                 CHAR(10)+ ‘-----------------------------‘
  3. STR():

    Converts a numeric value to a string. Similar to STR(5)= CONVERT(CHAR(1), 5) It accepts three arguments: the numeric value, the overall length, and the number of decimal positions.
    Syntax: STR(float_expression, character_length, number_of_decimal_places)
    If the integer part of the number and decimal positions is shorter than the overall length, the result is left-padded with spaces.
    1. SELECT STR(123.4, 8, 4) è 23.4000
    2. SELECT STR(100.325,7,2) è 100.33
    3. SELECT STR(100.325,7) è100
    4. SELECT STR(1, 6, 4) è 1.0000
    5. SELECT STR(11111111, 10, 4) è 11111111.0

2. Extracting Sub-string

  1. LEFT() :

    Returns sub-string with given length from the left-hand side of given string.
    Syntax:
    LEFT ( character_expression , integer_expression )
    1. To extract N-number of chars from left.

      SELECT LEFT('1234',1)=> 1
      SELECT LEFT('1234',2)=>12
      SELECT LEFT('1234',3)=> 123
      SELECT LEFT('welcome to www.abc.com', 8) => welcome
    2. You can use it to perform pattern matching. E.g., Find the employees whose name beings with 'A' letter.

      SELECT *
      FROM dbo.Customers
      WHERE LEFT(Cname, 1) = N'A';
  2. RIGHT():

    Returns sub-string with given length from the right-hand side of given string. Syntax:
    RIGHT( character_expression , integer_expression )
    1. To extract N-number ofchars from right

      SELECT LEFT('1234',1)=>4
      SELECT LEFT('1234',2)=>34
      SELECT LEFT('1234',3)=>234
    2. You can use it to perform pattern matching.
      Q: Find the employees whose name ends with ‘n’ letter.

      SELECT *
      FROM dbo.Customers
      WHERE RIGHT(Cname, 1) = N'n';
  3. SUBSTRING():

    Returns a part of given string. Syntax:
    SUBSTRING(string, start_position, length)
    1. Extracting part of given string:

      SELECT SUBSTRING("ABCDEFGH",2,3)èBCD
    2. Extracting first word only.

      SELECT SUBSTRING('Welcome Hello', 1, CHARINDEX(' ', 'Welcome Hello') - 1) => Welcome
    3. To extracting all words except first.

      SELECT SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName))
      => If @FullName=‘ The SQL Server 2005’, then output=’SQL Server 2005’

3. Find and Replace string

  1. CHARINDEX():

    Returns 0 if no-match, otherwise position of found substring. Comparison is case-insensitive basis.

    Syntax

    CHARINDEX(substring,SuperString,[searchPosition])
    Where return Value always < searchPosition

    Examples

    1. SELECT CHARINDEX(' ', 'welcome abcd ') => 8
    2. SELECT CHARINDEX('sql', ' SQL Server') => 2
    3. SELECT CHARINDEX(‘AB’,’ABCDEF’,3); => 0
      //Because after 3rd letter no ‘AB’
    4. You can search any kind of letter.

      select CHARINDEX('-', '(559) 555-1212')=> 10
  2. PARTINDEX():

    Searching strings within another string using wild-card characters. Wild-card characters: “%”= any no. of chars & “_” = Any single char.

    Syntax:

    PATINDEX ( '%pattern%' , expression )

    Examples:

    1. SELECT PATINDEX('%yahoo%','This is www.yahoo.com') => 13
    2. SELECT PATINDEX('%567%', '0123456789') => 6
    3. SELECT PATINDEX('%v_r%', 'SQL Server') => 8
  3. REPLACE():

    Replaces the all occurrence of given string with sub-string.

    Syntax

    REPLACE(MainString, string_to_Find, String_to_replace)

    Examples:

    SELECT REPLACE("www.yahoo.com yahoo","yahoo","YAHOO")
    => www.YAHOO.com YAHOO

4. STRING COMPARISION

  1. DIFFERENCE()

    Returns an integer value that indicates the difference between the SOUNDEX value of two character expressions. i.e,DIFFERENCE(s1,s2) = ABS(SOUNDEX(s1) – SOUNDEX(s2))
    The return value is between 0-4 reflects closeness of two strings. 4 means perfect match and close and 0 means poorly close.

    Syntax

    DIFFERENCE (string1,string2)

    Examples:

    Actually it differentiates the string based on pronunciation; not on spelling nor case difference. E.g.,DIFFERENCE(sion, shun)è4 (i.e, same no difference)

    1. SELECT DIFFERENCE('Dewson', 'Joosun') => 3
    2. SELECT DIFFERENCE('aaaa','1111') => 0
    3. SELECT DIFFERENCE('Dewson', 'aaaa') => 1
    4. SELECT DIFFERENCE('Welcome', 'welcom') =>4
    5. SELECT DIFFERENCE('Welcome', 'wecom') => 3
    6. SELECT DIFFERENCE('To', 'Two') => 4(i.e,perfect match)
    7. SELECT DIFFERENCE('nite', 'knight') => 1 (i.e,low)
    8. SELECT DIFFERENCE("Kathy Ward","kathy WARD") => 4
    9. SELECT DIFFERENCE("KATHY Ward","Cathy Ward") => 3
    10. SELECT DIFFERENCE("KATHY Ward","Cathie Ward")=> 3
  2. SOUNDEX():

    Returns a four-character(SOUNDEX) code to evaluate the similarity of two strings. Internally DIFFERENCE() uses SOUNDEX() to evaluating string comparison.

    Examples

    1. SELECT SOUNDEX(‘tion’) => S500
    2. SELECT DIFFERENCE(SOUNDEX('tion'), SOUNDEX('tion')) => 4
    3. SELECT SOUNDEX("Robin Dewson")=SOUNDEX("Robyn Jewshoon")
      => R150 R10
    4. SELECT SOUNDEX('Fleas'),SOUNDEX('Fleece'), SOUNDEX('Peace'),SOUNDEX('Peas')
      => 420 420 400 400
    5. SELECT CASE
        WHEN SOUNDEX('Robin Dewson')=SOUNDEX('Robyn Jewshoon')
        THEN 'EQUAL' ELSE 'UN-EQUAL'
      END
      => EQUAL

5. CASE CONVERSION

  1. LOWER()

    Returns lower case string for given string.

    Examples

    SELECT LOWER('ABCDEE') => abcdee
  2. UPPER()

    Returns upper case string for given string.

    Examples

    SELECT UPPER(LOWER('abCDE')) => ABCDE

    Changing to Sentence case

    SELECT UPPER(LEFT("this Is a tEst.",1)+
               LOWER(SUBSTRING("this Is a tEst.",2,20))
    => This is a test.

6.Space Trimming and Padding

  1. LTRIM() and RTRIM()

    Return the trimmed string of given string.
    1. SELECT LTRIM(‘ abcd’) => abcd
    2. SELECT RTRIM(‘WEL ‘)+LEFT(‘ COME’) => WELCOME
  2. SPACE():

    To get given no. of blank spaces.
    SELECT 'Give me some' + SPACE(6) + 'space.'
  3. STUFF()

    positional based insertion/replacement of text.

    Syntax:

    STUFF(OriginalString, position, no_of_overwrites, subs-string)
    1. For replacing “BCD”with "sho"

      SELECT STUFF('ABCDEFGHIJKL', 2, 3, ' sho') => AshoEFGHIJKL
    2. For inserting 'sho' after D

      SELECT STUFF('ABCDEFGHIJKL', 4, 0, 'sho') => ABCshoDEFGHIJKL

7. Determining Length and Reverse

  1. LEN()

    Returns the size of given string including blank spaces. bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.

    Syntax:

    LEN( string_expression )

    Examples:

    1. select LEN('SQL Server')=>10
  2. REVERSE()

    Returns the reverse of given string.

    Syntax

    REVERSE( char expression)

    Exampels:

    1. SELECT REVESE(‘abc’) =>cba
    2. SELECT Name, REVERSE(Name)as NameReversed
      FROM employee

2 comments: