CODEWITHSUNDEEP
sqlsql-server
user30375
user30375

Reputation: 23

How can I split a string value and insert it in a different column in the same row?

i have column like this (all in varchar)

SIGN ON SIGN OFF              SIGN IN      SIGN OFF
-----------------------       --------   -----------
01-05-2015 / 20-04-2016         NULL        NULL

AND i want to do this:

SIGN ON SIGN OFF              SIGN IN      SIGN OFF
 -----------------------      --------     -----------
01-05-2015 / 20-04-2016       01-05-2015   20-04-2016

i try with string split from sql 2016 but he had on another line the values not the same.

how can i do it?

Upvotes: 1

Views: 106

Answers (4)

user30375
user30375

Reputation: 23

if i enter manually both answers worked. but for some reason executing was give me an error. so i give up from trying in the Sql code i did in c#.

here my solution that worked fo all cells.

 DBHelper.DBHelper da = new DBHelper.DBHelper();

            DataTable dt = new DataTable();
            dt = da.getAllEmbarques();


            foreach (DataRow row in dt.Rows)
            {

                try
                {
                    List<SqlParameter> p = new List<SqlParameter>();
                    string nospaces = row[5].ToString().Replace(" ", string.Empty);
                    string[] newsplit = nospaces.Split('/');
                    if (newsplit.Count() == 1)
                    {
                        string[] newsplit1 = newsplit[0].ToString().Split('-');

                        DateTime dt1 = new DateTime(Int32.Parse(newsplit1[0]), Int32.Parse(newsplit1[1]), Int32.Parse(newsplit1[2]));
                        SqlParameter parameter = new SqlParameter("@SIGNIN",SqlDbType.DateTime);
                        parameter.IsNullable = true;
                        parameter.Value = dt1;
                        p.Add(parameter);
                        parameter = new SqlParameter("@SIGNOFF", SqlDbType.DateTime);
                        parameter.IsNullable = true;
                        parameter.Value = DBNull.Value;
                        p.Add(parameter);                     
                        p.Add(new SqlParameter("@id", row[7].ToString()));


                        da.UpdateStringsplitEmbarques(p.ToArray());
                    }
                    else
                    if (newsplit.Count() == 2)
                    {
                        string[] newsplit1 = newsplit[0].ToString().Split('-');
                        string[] newsplit2 = newsplit[1].ToString().Split('-');

                        DateTime dt1 = new DateTime(Int32.Parse(newsplit1[0]), Int32.Parse(newsplit1[1]), Int32.Parse(newsplit1[2]));
                        DateTime dt2 = new DateTime(Int32.Parse(newsplit2[0]), Int32.Parse(newsplit2[1]), Int32.Parse(newsplit2[2]));
                        SqlParameter parameter = new SqlParameter("@SIGNIN", SqlDbType.DateTime);
                        parameter.IsNullable = true;
                        parameter.Value = dt1;
                        p.Add(parameter);
                        parameter = new SqlParameter("@SIGNOFF", SqlDbType.DateTime);
                        parameter.IsNullable = true;
                        parameter.Value = dt2;
                        p.Add(parameter);
                        p.Add(new SqlParameter("@id", row[7].ToString()));
                        da.UpdateStringsplitEmbarques(p.ToArray());
                    }




                }
                catch
                {

                }
            }

thanks you all for the time spending answering my question.

best regards

Upvotes: 0

Apoorv
Apoorv

Reputation: 1

You can try the following query.

select substring([SIGN ON SIGN OFF],1,charindex('/',[SIGN ON SIGN OFF])-2) as sign_in, 
substring([SIGN ON SIGN OFF],charindex('/',[SIGN ON SIGN OFF])+1,len([SIGN ON SIGN OFF])) as sign_off

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can use string functions:

select col1,
       left(col1, charindex('/', col1) - 1),
       stuff(col1, 1, charindex('/', col1) + 1, '')
from t;

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try below -

DEMO

  select 
      left(colname,charindex('/',colname)-1) as signon, 
      right(colname,charindex('/',colname)-1) as signoff
  from tablename

OUTPUT:

signon       signoff
01-05-2015   20-04-2016

Upvotes: 1

Related Questions