Got these very useful references from http://sqltutorials.blogspot.com/2007/06/sql-string-functions.html

SQL String Functions

Sql string function is a built-in string function.
It perform an operation on a string input value and return a string or numeric value.
Below is All built-in Sql string function :
ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX, SPACE, CHARINDEX, REPLACE, STR, DIFFERENCE, QUOTENAME, STUFF, LEFT, REPLICATE, SUBSTRING, LEN, REVERSE, UNICODE, LOWER, RIGHT, UPPER, LTRIM, RTRIM
Example SQL String Function - ASCII
- Returns the ASCII code value of a keyboard button and the rest etc (@,R,9,*) .
Syntax - ASCII ( character)
SELECT ASCII(’a') — Value = 97
SELECT ASCII(’b') — Value = 98
SELECT ASCII(’c') — Value = 99
SELECT ASCII(’A') — Value = 65
SELECT ASCII(’B') — Value = 66
SELECT ASCII(’C') — Value = 67
SELECT ASCII(’1′) — Value = 49
SELECT ASCII(’2′) — Value = 50
SELECT ASCII(’3′) — Value = 51
SELECT ASCII(’4′) — Value = 52
SELECT ASCII(’5′) — Value = 53
Example SQL String Function - SPACE
-Returns spaces in your SQL query (you can specific the size of space).
Syntax - SPACE ( integer)
SELECT (’SQL’) + SPACE(0) + (’TUTORIALS’)
– Value = SQLTUTORIALS
SELECT (’SQL’) + SPACE(1) + (’TUTORIALS’)
– Value = SQL TUTORIALS
Example SQL String Function - CHARINDEX
-Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] )
SELECT CHARINDEX(’SQL’, ‘Well organized understand SQL tutorial’)
– Value = 27
SELECT CHARINDEX(’SQL’, ‘Well organized understand SQL tutorial’, 20)
– Value = 27
SELECT CHARINDEX(’SQL’, ‘Well organized understand SQL tutorial’, 30)
– Value = 0 (Because the index is count from 30 and above)
Example SQL String Function - REPLACE
-Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( ’string1′ , ’string2′ , ’string3′ )
SELECT REPLACE(’All Function’ , ‘All’, ‘SQL’)
– Value = SQL Function


Example SQL String Function - QUOTENAME
-Returns a Unicode string with the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
Syntax - QUOTENAME ( ’string’ [ , 'quote_character' ] )
SELECT QUOTENAME(’Sql[]String’)
– Value = [Sql[]]String]

Example SQL String Function - STUFF
- Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 , startindex , length , string2 )
SELECT STUFF(’SqlTutorial’, 4, 6, ‘Function’)
– Value = SqlFunctional
SELECT STUFF(’GoodMorning’, 5, 3, ‘good’)
– Value = Goodgoodning


Example SQL String Function - LEFT
-Returns left part of a string with the specified number of characters.
Syntax - LEFT ( string , integer)
SELECT LEFT(’TravelYourself’, 6)
– Value = Travel
SELECT LEFT(’BeautyCentury’,6)
– Value = Beauty


Example SQL String Function - RIGHT
-Returns right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT(’TravelYourself’, 6)
– Value = urself
SELECT RIGHT(’BeautyCentury’,6)
– Value = Century

Example SQL String Function - REPLICATE
-Repeats string for a specified number of times.
Syntax - REPLICATE (string, integer)
SELECT REPLICATE(’Sql’, 2)
– Value = SqlSql


Example SQL String Function - SUBSTRING
-Returns part of a string.
Syntax - SUBSTRING ( string, startindex , length )
SELECT SUBSTRING(’SQLServer’, 4, 3)
– Value = Ser


Example SQL String Function - LEN
-Returns number of characters in a string.
Syntax - LEN( string)
SELECT LEN(’SQLServer’)
– Value = 9


Example SQL String Function - REVERSE
-Returns reverse a string.
Syntax - REVERSE( string)
SELECT REVERSE(’SQLServer’)
– Value = revreSLQS


Example SQL String Function - UNICODE
-Returns Unicode standard integer value.
Syntax - UNICODE( char)
SELECT UNICODE(’SqlServer’)
– Value = 83 (it take first character)
SELECT UNICODE(’S')
– Value = 83

Example SQL String Function - LOWER
-Convert string to lowercase.
Syntax - LOWER( string )
SELECT LOWER(’SQLServer’)
– Value = sqlserver


Example SQL String Function - UPPER
-Convert string to Uppercase.
Syntax - UPPER( string )
SELECT UPPER(’sqlserver’)
– Value = SQLSERVER


Example SQL String Function - LTRIM
-Returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )
SELECT LTRIM(’ sqlserver’)
– Value = ’sqlserver’ (Remove left side space or blanks)
Example SQL String Function - RTRIM
-Returns a string after removing leading blanks on Right side.
Syntax - RTRIM( string )
SELECT RTRIM(’SqlServer ‘)
– Value = ‘SqlServer’ (Remove right side space or blanks)

The XML File

<client_order ordID="14" supplierID="000002">
  <item_order itoItemID="12" itoItemQty="1" itoItemPrice="35.99" itoTaxAmount="3.00" itemCode="LD3162" />
  <item_order itoItemID="3" itoItemQty="0" itoItemPrice="29.99" itoTaxAmount="3.00" itemCode="LD3278" />
  <item_order itoItemID="7" itoItemQty="0" itoItemPrice="31.99" itoTaxAmount="3.00" itemCode="LD3226" />
  <item_order itoItemID="10" itoItemQty="0" itoItemPrice="31.99" itoTaxAmount="3.00" itemCode="LD3226" />
  <item_order itoItemID="9" itoItemQty="0" itoItemPrice="29.99" itoTaxAmount="3.00" itemCode="LD3226" />
</client_order>

The CODE ;o)

//Get the XML
                    string responseXML = r["Response"].ToString();

                    try
                    {
                        XmlDocument contentXML = new XmlDocument();
                        contentXML.LoadXml(responseXML);

                        //Get the attribute values from the root node <client_order>
                        XmlNode client_orderNode = contentXML.DocumentElement.SelectSingleNode("//client_order");
                        string supplierID = client_orderNode.Attributes["supplierID"].Value.ToString();
                        string ordID = client_orderNode.Attributes["ordID"].Value.ToString();

                        string supplierName = CommonDBCommands.SelectValue(connectionString, "suppliers", "supplierName",
                            "supplierID", supplierID);

                        //GET the order id and supplier as recorded in the XML response.
                        Debug.Print("Supplier ID = " + supplierID);
                        Debug.Print("Order ID = " + ordID);

                        //read each element <item_order> in <client_order> and get the attribute values
                        foreach (XmlNode node in client_orderNode)
                        {
                            XmlElement item_order = (XmlElement)node;

                            string itemID = "";
                            string itemQty = "";
                            string itemPrice = "";
                            string itemTaxAmount = "";
                            string itemCode = "";

                            if (item_order.HasAttributes)
                            {
                                itemID = item_order.Attributes["itoItemID"].InnerText;
                                itemQty = item_order.Attributes["itoItemQty"].InnerText;
                                itemPrice = item_order.Attributes["itoItemPrice"].InnerText;
                                itemTaxAmount = item_order.Attributes["itoTaxAmount"].InnerText;
                                itemCode = item_order.Attributes["itemCode"].InnerText;

                                if (itemID == hItemID)
                                {
                                    DataRow row = dt.NewRow();
                                    row["SupplierName"] = supplierName;
                                    row["itemID"] = itemID;
                                    row["Qty"] = Convert.ToDecimal(itemQty);
                                    row["price"] = Convert.ToDecimal(itemPrice);
                                    row["taxAmount"] = Convert.ToDecimal(itemTaxAmount);
                                    row["itemCode"] = itemCode;

                                    dt.Rows.Add(row);
                                }
                            }

                            Debug.Print("item ID  = " + itemID);
                            Debug.Print("price  = $" + itemPrice);

                        }
                    }
                    catch (Exception ex)
                    {
                        Response.Write(ex.Message.ToString());
                    }

protected void LogOut()
    {
        Session.Abandon();
        string nextpage = "Logoutt.aspx";
        Response.Write("<script language=javascript>");

        Response.Write("{");
        Response.Write(" var Backlen=history.length;");

        Response.Write(" history.go(-Backlen);");
        Response.Write(" window.location.href='" + nextpage + "'; ");

        Response.Write("}");
        Response.Write("</script>");

    }

txtSubject.BackColor = ColorTranslator.FromHtml("#C4C4C4");