Split String

Question

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.

Nilanka Dharmadasa · Answer

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.

Hiren Soni · Answer

Kirtan Patel · Answer

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(); }

Srinivas Kotra · Answer

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<Len(@FundIDs) Begin Set @Pos1 = CharIndex(',',@FundIDs,@Pos1) Insert @Tbl_IDs Select Substring(@FundIDs,@Pos2,@Pos1-@Pos2) Set @Pos2=@Pos1+1 Set @Pos1 = @Pos1+1 End Return End Thanks, Srinivas Kotra.

Lalit M · Answer

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