27,579
社区成员
发帖
与我相关
我的任务
分享
UPDATE tB
SET Price =substring (Price,0,case when ISNUMERIC(Price)=0 then patindex('%[^0-9]%',Price) else Price end), unit=
case when Price like '%/%' then substring( Price,CHARINDEX('/',Price,0)+1,LEN(Price)-CHARINDEX('/',Price,0))
else NULL end
from tB
SELECT * FROM tB
再改改
select ProductID Price,case when patindex('%[0-9]%',Price)>=0
then subString(price,patindex('%[0-9 .]%',Price)-1,patindex('%[^0-9 .]%',Price))
else null end price1
from dbo.Product where ProductID=58164
select ProductID,Price from dbo.Product where ProductID=58164
--> 测试数据:@t
drop table tB
CREATE TABLE tB ([ID] int,[Price] varchar(8),unit varchar(8))
insert tB
select 1,'100元/件',NULL union all
select 2,'80元/卷',NULL union all
select 3,'电议' ,NULL union all
select 4,'优惠',NULL union all
select 5,null,NULL union all
select 6,'面议',NULL union all
select 7,'咨询',NULL
UPDATE tB
SET Price =substring (Price,0,patindex('%[^0-9]%',Price)), unit=
case when Price like '%/%' then substring( Price,CHARINDEX('/',Price,0)+1,LEN(Price)-CHARINDEX('/',Price,0))
else NULL end
from tB
SELECT * FROM tB
------------------------------------
ID Price unit
1 100 件
2 80 卷
3 NULL
4 NULL
5 NULL NULL
6 NULL
7 NULL
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (id int, price varchar(20))
insert into tb
select 1, '100元/件' union all
select 2, '80元/卷' union all
select 3, '电议' union all
select 4, '优惠' union all
select 5, null union all
select 6, '面议' union all
select 7, '咨询'
select id,Price= case when PATINDEX('%[^0-9]%',price)-1 > 0 then LEFT(price,CHARINDEX('/',price)-2)
else '' end,
Unit= case when PATINDEX('%[^0-9]%',price)-1 > 0 then right(price,len(price)-CHARINDEX('/',price))
else '' end
from tb
id Price Unit
1 100 件
2 80 卷
3
4
5
6
7
IF OBJECT_ID('[TB]') IS NOT NULL
DROP TABLE [TB]
CREATE TABLE TB
(
ID INT,PRICE NVARCHAR(150)
)
INSERT INTO TB
SELECT 1,'100元/件' UNION
SELECT 2,'80元/卷' UNION
SELECT 3,'电议' UNION
SELECT 4,'优惠' UNION
SELECT 5,'' UNION
SELECT 6,'面议' UNION
SELECT 7,'咨询'
IF OBJECT_ID('[TB_T]') IS NOT NULL
DROP TABLE [TB_T]
CREATE TABLE [TB_T]
(
ID INT ,
PRICE FLOAT,
UNIT VARCHAR(80)
)
INSERT INTO TB_T
SELECT ID
,SUBSTRING(PRICE,0,(case when CHARINDEX('/',PRICE)-1<=0 then 0 else CHARINDEX('/',PRICE)-1 end)) as price
,SUBSTRING(PRICE,case when CHARINDEX('/',PRICE)=0 then LEN(PRICE)+1 else CHARINDEX('/',PRICE)+1 end ,LEN(PRICE)) as unit
FROM TB
SELECT * FROM TB_T
if object_id('tb') is not null
drop table tb
go
create table tb(id int identity primary key,price varchar(10))
insert into tb select '100.5元/件' union all
select '80元/卷' union all
select '电议' union all
select '优惠' union all
select '咨询' union all
select '面议' union all
select ''
select id,price,case when patindex('%[0-9]%',Price)>0
then subString(price,patindex('%[0-9 .]%',Price)-1,patindex('%[^0-9 .]%',Price))
else null end price1
from tb
--> 测试数据:@t
declare @t table([ID] int,[Price] varchar(8))
insert @t
select 1,'100元/件' union all
select 2,'80元/卷' union all
select 3,'电议' union all
select 4,'优惠' union all
select 5,null union all
select 6,'面议' union all
select 7,'咨询'
select ID,substring (Price,0,patindex('%[^0-9]%',Price)), unit=
case when Price like '%/%' then substring( Price,CHARINDEX('/',Price,0)+1,LEN(Price)-CHARINDEX('/',Price,0))
else NULL end
from @t
这样就好了
--> 测试数据:@t
declare @t table([ID] int,[Price] varchar(8))
insert @t
select 1,'100元/件' union all
select 2,'80元/卷' union all
select 3,'电议' union all
select 4,'优惠' union all
select 5,null union all
select 6,'面议' union all
select 7,'咨询'
select ID,Price, unit=
case when Price like '%/%' then substring( Price,CHARINDEX('/',Price,0)+1,LEN(Price)-CHARINDEX('/',Price,0))
else NULL end
from @t
====================
ID Price unit
1 100元/件 件
2 80元/卷 卷
3 电议 NULL
4 优惠 NULL
5 NULL NULL
6 面议 NULL
7 咨询 NULL
if object_id('tb') is not null
drop table tb
go
create table tb(id int identity primary key,price varchar(10))
insert into tb select '100元/件' union all
select '80元/卷' union all
select '电议' union all
select '优惠' union all
select '咨询' union all
select '面议' union all
select ''
go
alter table tb add unit varchar(10);
go
update tb
set unit=case when ISnumeric(left(price,1))=1
then subString(price,charindex('/' ,price)+1,len(price)) else '' end,
price=case when ISnumeric(left(price,1))=1
then subString(price,1,charindex('/' ,price)-2) end
alter table tb alter column price decimal(9,2)
select * from tb