Split String

avani raval

Hi friends , In my database i have a column named "Name" in this column i had stored firstname and lastname with '_' seprator. Ex : "XYZ_ABC". But now in my select query i would like to have only lastname to fetch. For that what should i do? How to splite the value in select query ? Waiting for the reply. Thank you.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nilanka Dharmadasa17y ago
Hi friend,

If your database is SQL server :

select SUBSTRING(column_name, CHARINDEX('_', column_name) + 1, LEN(column_name) - CHARINDEX('_', column_name)) from table_name

If your database is Oracle :

select substr(column_name,instr(column_name,'_') + 1) from table_name

Replace 'comun_name' with the name of your column and table_name with the name if your table.

If you find my answer useful, please check 'Do you like this answer' check box.
0
Hiren Soni17y ago
0
Kirtan Patel17y ago

Friend,
You can Do this By two Ways By using SQL Server in which you have to construct your own function in database or you can do it Code Behind Side.

I have Shown you Second Way here because it easiest to understand ...

        private void button1_Click(object sender, EventArgs e)

        {

            List<string> LastNames = new List<string>();

            SqlConnection con = new SqlConnection("your connection string");

            con.Open();

            SqlCommand comm = new SqlCommand("select name from students", con);

            SqlDataReader reader = comm.ExecuteReader();

            LastNames.Clear();

            while (reader.Read())

            {

                string[] name = reader[0].ToString().Split('_');

                LastNames.Add(name[0]);

            }

            dataGridView1.DataSource = LastNames;

            con.Close();

        }

0
Srinivas Kotra17y ago
Hi, This function may helps you. try your own way. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE Function [dbo].[Split_String] (@FundIDs Varchar(MAX) ) Returns @Tbl_IDs Table (ID varchar(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS) As Begin Set @FundIDs = @FundIDs + ',' Declare @Pos1 Int Declare @pos2 Int Set @Pos1=1 Set @Pos2=1 While @Pos1
0
Lalit M17y ago
You can try this code sample

class Customer
{
public string FirstName { get; set; }
public string LastName { get; set; }
public void NameSplit(string name)
{
    if(name.Length > 0)
    {
      // Check for a comma
      if(name.IndexOf(",") > 0)
      {
        LastName = name.Substring(0, name.IndexOf(",")).Trim();
        FirstName = name.Substring(name.IndexOf(",") + 1).Trim();
      }
      else if(name.IndexOf(" ") > 0)
      {
        FirstName = name.Substring(0, name.IndexOf(" ")).Trim();
        LastName = name.Substring(name.IndexOf(" ") + 1).Trim();
      }
    }
}
}
reference link
0