SQL Server Fucntion to Split Comma-Seperated Strings into table

Split function in general would have comma-separated string value to be split into individual strings.

The below Split function is Table-valued function which would help us splitting comma-separated (or any other delimiter value) string to individual string.

CREATE  FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000)) 
as  
begin 
declare @idx int
declare @slice varchar(8000) 
select @idx = 1 
if len(@String)<1 or @String is null return 
while @idx!= 0 
begin 
set @idx = charindex(@Delimiter,@String) 
if @idx!=0 
set @slice = left(@String,@idx - 1) 
else 
set @slice = @String 
if(len(@slice)>0)
insert into @temptable(Items) values(@slice) 
set @String = right(@String,len(@String) - @idx) 
if len(@String) = 0 break 
end 
return

split function can be Used as

       select * from dbo.split ( '9899999999,9877889876,9865489678' , ',' )


would return

Hope this helps.

How to Convert a numbers to words in asp.net

This article explains about how to convert a numeric value to words.

For Example: Numeric: 500
                       In Words: Five hundred Only


Write a Code:

staticclass NumberToWord
{ 
  private static string[] _ones =
   {
     "zero",
     "one",
     "two",
     "three",
     "four",
     "five",
     "six",
     "seven",
     "eight",
     "nine"
   }; 
 private static string[] _teens =
  { 
     "ten",
     "eleven",
     "twelve",
     "thirteen",
     "fourteen",
     "fifteen",
     "sixteen",
     "seventeen",
     "eighteen",
     "nineteen"
  };
 private static string[] _tens =
  {
     "",
     "ten",
     "twenty",
     "thirty",
     "forty",
     "fifty",
     "sixty",
     "seventy",
     "eighty",
     "ninety"
  };
 private static string[] _thousands =
  {
     "",
     "thousand",
     "million",
     "billion",
     "trillion",
     "quadrillion"
  };

 public static string Convert(decimal value)
  { 
    string digits, temp;
    bool showThousands = false;
    bool allZeros = true;           
    StringBuilder builder = new StringBuilder();
    digits = ((long)value).ToString(); 
    for(int i = digits.Length-1; i >= 0; i--)
    {
      int ndigit = (int)(digits[i] - '0');
      int column = (digits.Length - (i + 1));
      switch (column % 3)
      { 
  case 0:   
       showThousands = true; 
       if (i == 0)
         {
           temp =String.Format("{0} ", _ones[ndigit]);
         }
       else if (digits[i - 1] == '1')
         {
           temp = String.Format("{0} ", _teens[ndigit]);
           i--;
         }
       else if (ndigit != 0)
        {
          temp = String.Format("{0} ", _ones[ndigit]);
        }
       else
        {
          temp = String.Empty;
          if (digits[i - 1] != '0' || (i > 1 && digits[i - 2] != '0'))
          showThousands = true;
          else
          showThousands = false;
        }
       if (showThousands)
       {
        if (column > 0)
        {
         temp = String.Format("{0}{1}{2}",
                temp,_thousands[column / 3], allZeros ? " " : " ");
        }
        allZeros = false;
       }
       builder.Insert(0, temp);
       break;

 case 1:
        if (ndigit > 0)
        {
         temp = String.Format("{0}{1}",_tens[ndigit],
         (digits[i + 1] != '0') ? "-" : " ");builder.Insert(0, temp);
       }
       break;
 
case 2:
      if (ndigit > 0)
        {
         temp = String.Format("{0} hundred ", _ones[ndigit]);
                    builder.Insert(0, temp);
       }
       break;
     }}
   builder.AppendFormat("only", (value - (long)value) * 100);
   return String.Format("{0}{1}",
   Char.ToUpper(builder[0]),
   builder.ToString(1, builder.Length - 1));
  }  
}

 
Add following code on Button_Click event

decimal number;
if (!string.IsNullOrEmpty(txtAmount.Text) && decimal.TryParse(txtAmount.Text.Trim(), out number))
{
   lblAmountChar.Text = "(" + NumberToWord.Convert(number) + ")";
}

It convert given number to words. Thanks.

How to Import Excel File into SQL Server using SQLBULK in ASP.net

This example explains how to upload excel file, read Excel file data,  save Excel file data and import into SQL Server using SQLBULK in ASP.Net.

Step:1 Create a Excel file like:

Step:2 Create a Sql table in database like:

Step:3 Now, add the code in "Default.aspx"

<asp:FileUpload ID="fupUpload" runat="server" />

<asp:Button ID="btnImport" Font-Bold="true" ForeColor="White"

BackColor="#136671" Height="23px" runat="server" Text="Import Excel Data"
onclick="btnImport_Click" />


Step:4  Add the code in "Default.aspx.cs"

Add these NameSpace

using System.IO;
using System.Data.OleDb;
using System.Data;


Write the code in Click Event of Import Button

protected void btnImport_Click(object sender, EventArgs e)
{
 string strFilepPath;
 DataSet ds = new DataSet();
 string strConnection = ConfigurationManager.ConnectionStrings
                          ["connectionString"].ConnectionString;
 if (fupUpload.HasFile)
 {
  try
  {
    FileInfo fi = new FileInfo(fupUpload.PostedFile.FileName);
    string ext = fi.Extension;
    if (ext == ".xls" || ext == ".xlsx")
    {
     string filename = Path.GetFullPath(fupUpload.PostedFile.FileName);
     string DirectoryPath = Server.MapPath("~/UploadExcelFile//");
     strFilepPath = DirectoryPath + fupUpload.FileName;      
     Directory.CreateDirectory(DirectoryPath);
     fupUpload.SaveAs(strFilepPath);   
     string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" 
                      + strFilepPath + ";Extended Properties=\"Excel 12.0 
                      Xml;HDR=YES;IMEX=1\"";
     OleDbConnection conn = new OleDbConnection(strConn);
     conn.Open();     
     OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
     OleDbDataAdapter da = new OleDbDataAdapter(cmd);
     da.Fill(ds);
     DeleteExcelFile(fupUpload.FileName); // Delete File Log
     SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection, 
                                   SqlBulkCopyOptions.KeepIdentity);
     sqlBulk.DestinationTableName = "Table_1";
     sqlBulk.WriteToServer(ds.Tables[0]);
     conn.Close();
     sqlBulk.Close();      
     ScriptManager.RegisterStartupScript(Page, GetType(), "script1",  
        "alert('Excel file successfully imported into DB');", true);
     return;
    }     
    else
    {
      ScriptManager.RegisterStartupScript(Page, GetType(), "script1",   
                    "alert('Please upload excel file only');", true);
     return;
    }
  }   
  catch (Exception ex)
   {
    DeleteExcelFile(fupUpload.FileName);       
    ScriptManager.RegisterStartupScript(Page, GetType(), "script1",   
      "alert('error occured: " + ex.Message.ToString() + "');", true);
    return;
   }
  }  
 else
  { 
    ScriptManager.RegisterStartupScript(Page, GetType(), "script1",   
                        "alert('Please upload excel file');", true);
   return;
  }
}

protected void DeleteExcelFile(string Name)
{              
 if (Directory.Exists(Request.PhysicalApplicationPath +   
                                           "UploadExcelFile\\"))
   {      
    string[] logList = Directory.GetFiles(Request.PhysicalApplicationPath 
                       + "UploadExcelFile\\", "*.xls");
     foreach (string log in logList)
      {         
        FileInfo logInfo = new FileInfo(log);
        string logInfoName = logInfo.Name.Substring(0,  
                             logInfo.Name.LastIndexOf('.'));
        if (logInfoName.Length >= Name.Length)
         {            
          if (Name.Equals(logInfoName.Substring(0, Name.Length)))
           {
             logInfo.Delete();
           }
         }
      }
   }
}





and the result will be be like as: