create table tb3
(id int identity(1,1) primary key clustered,
l_name varchar(100))
--tb3数据
id l_name
----------- -------------------------------------------------------
1 gsdg#123
2 fh#3435
3 fghdfgh#67867
4 lkhgvhg#7688796
SELECt id,left(l_name,charindex('#',l_name)-1) l_name }
from tb3
union all
SELECt id,substring(l_name,charindex('#',l_name)+1,len(l_name)-charindex('#',l_name)+1) l_name
from tb3
--显示结果
id l_name
----------- -----------------------------------------------------
1 gsdg
2 fh
3 fghdfgh
4 lkhgvhg
1 123
2 3435
3 67867
4 7688796
