SqlTutorial-1 : Lesson-11 : Class-2 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Built-in Sql 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
   3. 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