数据表中有一个字段是联系方式字段,里面包括Eamil、Phone、Fax三部分,我现在想把它拆分为三个字段,并把对应信息填入其中。我是在我inForm中制作的,我的想法是找到这个联系方式字段时,双击它,让它在原表中自动生成三个字段,并填入值,请问这个想法,能实现吗?有什么具体解决办法吗?
解决方案 »
- 求一条单表查询的复杂SQL语句??
- dajia bangbang 忙 sql wen ti
- SQL Update语句中根据天数计算周数的问题
- 如何把sql sever 2000的实例完全转移到另一台服务器?
- 邹建top n通用分页条件加入in语句会出错。
- 再次求教取出相同数据的问题!!
- 数据库服务器出现很多计划任务
- 求一条SQL语句。 谢谢!
- update,insert,delete三种情况都要触发时,触发器该怎么写?
- 我的windows 2000不能通过TCP/IP来连接NT上的SQL Server 6.5
- 两表连接查询问题,在线等,急....
- localhost或127.0.0.1登录不了的问题
--增加字段
alter table TB
add Eamil varchar(20)
add Eamil varchar(20),Phone varchar(20),Fax varchar(20)
--删除字段
alter table TB
drop column Eamil,Phone,Fax
{
using (SqlConnection con = new SqlConnection("Server=" + strServerName + ";database=" + strDataName + ";Uid=" + strUser + ";Pwd=" + strPwd))
{
sql = "select "+ str + " from "+strDataTable; //str代表联系方式字段
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader reader = cmd.ExecuteReader();
//取出联系方式一个集合以后,怎么拆分啊?
//拆分完,如何存放到三个新字段中呢?
}
}
insert into tb values('Email:aa·126.com Phone:025-123456 Fax:021-789456')
goselect
replace(parsename(replace(replace(col , '.' , '#'),' ','.'),3),'#','.'),
replace(parsename(replace(replace(col , '.' , '#'),' ','.'),2),'#','.'),
replace(parsename(replace(replace(col , '.' , '#'),' ','.'),1),'#','.')
from tbdrop table tb/*
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Email:aa·126.com Phone:025-123456 Fax:021-789456(所影响的行数为 1 行)*/create table tb(col varchar(100))
insert into tb values('Email:aa·126.com Phone:025-123456 Fax:021-789456')
goselect replace(parsename(replace(replace(col , '.' , '#'),' ','.'),3),'#','.') from tb
union all
select replace(parsename(replace(replace(col , '.' , '#'),' ','.'),2),'#','.') from tb
union all
select replace(parsename(replace(replace(col , '.' , '#'),' ','.'),1),'#','.') from tb
drop table tb/*
----------------------
Email:aa·126.com
Phone:025-123456
Fax:021-789456(所影响的行数为 3 行)*/
select
replace(parsename(replace(replace(col , '.' , '#'),' ','.'),3),'#','.'),
replace(parsename(replace(replace(col , '.' , '#'),' ','.'),2),'#','.'),
replace(parsename(replace(replace(col , '.' , '#'),' ','.'),1),'#','.')
from tbselect replace(parsename(replace(replace(col , '.' , '#'),' ','.'),3),'#','.') from tb
union all
select replace(parsename(replace(replace(col , '.' , '#'),' ','.'),2),'#','.') from tb
union all
select replace(parsename(replace(replace(col , '.' , '#'),' ','.'),1),'#','.') from tb至于这句
insert into tb values('Email:aa·126.com Phone:025-123456 Fax:021-789456')
是我的测试语句.
union all
select replace(parsename(replace(replace(col , '.' , '#'),' ','.'),2),'#','.') from tb
union all
select replace(parsename(replace(replace(col , '.' , '#'),' ','.'),1),'#','.') from tb如何对拆分完的数据,加入到对应字段中呢?还有,如果我想把每行的Email: Phone: Fax: 都省去,还要把 · 变为@ ,这个语法????
{
using (SqlConnection con = new SqlConnection("Server=" + strServerName + ";database=" + strDataName + ";Uid=" + strUser + ";Pwd=" + strPwd))
{
string sql = "select " + str + " from " + strDataTable;
sql += " alter table " + strDataTable + " add Email varchar(50),Phone varchar(50),Fax varchar(50)"; //生成三个新字段 con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader reader = cmd.ExecuteReader();
int temp = 0; //计数变量
while (reader.Read())
{
string strInfo = reader[temp].ToString().ToLower().Trim();//小写
int strLength = strInfo.Length; //长度 int emailNum = strInfo.IndexOf("email:");
int phoneNum = strInfo.IndexOf("phone:");
int faxNum = strInfo.IndexOf("fax:"); string email = strInfo.Substring(emailNum, phoneNum - emailNum).Replace("email:", "").Replace("·", "@");
string phone = strInfo.Substring(phoneNum, faxNum - phoneNum).Replace("phone:", "");
string fax = strInfo.Substring(faxNum, strLength - faxNum).Replace("fax:", "");
temp++; string insertsql = "insert into "+strDataTable+ "(Eamil,Phone,Fax) values("+ email+","+phone+","+fax +")";
cmd.ExecuteNonQuery();
}
reader.Close(); SqlDataReader sdr = cmd.ExecuteReader();
dataGridView1.DataSource = null;
dataGridView1.DataSource = sdr;
sdr.Close();
con.Close(); }
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
insert into tb2
select
replace(parsename(replace(replace(col , '.' , '#'),' ','.'),3),'#','.'),
replace(parsename(replace(replace(col , '.' , '#'),' ','.'),2),'#','.'),
replace(parsename(replace(replace(col , '.' , '#'),' ','.'),1),'#','.')
from tb1假设两表对应更新吗?
update tb2
set
c1 = replace(parsename(replace(replace(tb1.col , '.' , '#'),' ','.'),3),'#','.'),
c2 = replace(parsename(replace(replace(tb1.col , '.' , '#'),' ','.'),3),'#','.'),
c3 = replace(parsename(replace(replace(tb1.col , '.' , '#'),' ','.'),3),'#','.')
from tb2 , tb1
where tb2.关键字 = tb1.关键字
string insertsql = "insert into "+strDataTable+ "(Eamil,Phone,Fax) values("+ email+","+phone+","+fax +")";
SqlCommand cmd1 = new SqlCommand(insertsql, con); //这里报错,说已打开与当前冲突
cmd.ExecuteNonQuery();
刚才少贴了一句怎么改啊?请教!