27,579
社区成员
发帖
与我相关
我的任务
分享
IF object_id('tempdb..#test') IS NOT NULL
DROP TABLE #test
GO
CREATE TABLE #test
(
ID INT,
Val VARCHAR(32)
)
GO
INSERT INTO #test
SELECT 1 ,N'2-3吨' UNION ALL
SELECT 2 , N'40吨' UNION ALL
SELECT 3 , N'1吨' UNION ALL
SELECT 4 , N'0.2吨' UNION ALL
SELECT 5 , N'512-6456吨' union all
SELECT 5 , N'70-80吨'
declare @str varchar(max) = ''
select @str=@str+
case when replace(replace(val,'吨',''),'-','+') like '%+%' then QUOTENAME(replace(replace(val,'吨',''),'-','+'),'()') + '/2.0'
else replace(replace(val,'吨','') ,'-','+') end +' val union all select '
from #test
select @str = LEFT(@str,len(@str) -17)
exec('select '+ @str)
/*
(6 行受影响)
---------------------------------------
2.500000
40.000000
1.000000
0.200000
3484.000000
75.000000
(6 行受影响)
*/
IF object_id('tempdb..#test') IS NOT NULL
DROP TABLE #test
GO
CREATE TABLE #test
(
ID INT,
Val VARCHAR(32)
)
GO
INSERT INTO #test
SELECT 1 ,N'2-3吨' UNION ALL
SELECT 2 , N'40吨' UNION ALL
SELECT 3 , N'1吨' UNION ALL
SELECT 4 , N'0.2吨' UNION ALL
SELECT 5 , N'512-6456吨' union all
SELECT 5 , N'70-80吨'
declare @str varchar(max) = ''
select @str=@str+
case when replace(replace(val,'吨',''),'-','+') like '%+%' then replace(replace(val,'吨',''),'-','+') + '/2.0'
else replace(replace(val,'吨','') ,'-','+') end +' val union all select '
from #test
select @str = LEFT(@str,len(@str) -17)
exec('select '+ @str)
/*
(6 行受影响)
val
---------------------------------------
3.500000
40.000000
1.000000
0.200000
3740.000000
110.000000
(6 行受影响)
*/
if object_id('dbo.#tempt11') is not null drop table #tempt11
create table #tempt11(mainproductcount varchar(50))
insert into #tempt11
select '10頓' as mainproductcount union all
select '10-20頓' as mainproductcount union all
select '20' as mainproductcount union all
select '40頓' as mainproductcount union all
select '50頓' as mainproductcount union all
select '20' as mainproductcount
select replace( mainproductcount ,'頓','') as mainproductcount into #tempt12 from #tempt11
select case when CHARINDEX('-',mainproductcount)=0 then convert(numeric(12,2),mainproductcount )
else (convert(numeric(12,2),substring(mainproductcount,1,CHARINDEX('-',mainproductcount)-1 ))+
convert(numeric(12,2),substring(mainproductcount,(len(mainproductcount)-CHARINDEX('-',mainproductcount)+2),len(mainproductcount))))/2
end index_1 from #tempt12
轉換的優點麻煩..大概就這個意思IF object_id('test') IS NOT NULL
DROP TABLE test
GO
CREATE TABLE test
(
ID INT,
Val VARCHAR(32)
)
GO
INSERT INTO test
SELECT 1 ,N'2-3顿' UNION ALL
SELECT 2 , N'40' UNION ALL
SELECT 3 , N'1顿' UNION ALL
SELECT 4 , N'0.2 ' UNION ALL
SELECT 5 , N'512-6456吨'
--Step1:将Val字段定义为Varchar型,通过字符数与字节数来判断字段的组合类型
;WITH t1 AS(
SELECT id,SUBSTRING(val,1,LEN(val) - 1) 'val'
FROM test where LEN(RTRIM(val)) < DATALENGTH(RTRIM(val))
)
--去掉中文字符
UPDATE test
SET test.val = t1.val
FROM test INNER JOIN t1 ON t1.id = test.id
--Step 2: 对于xx-yy类型的组合分裂,可以通过isnumeric()函数判断
;WITH t2 AS(
SELECT id,val,
SUBSTRING(val,1,CHARINDEX('-',val)-1) 'leftVal',
SUBSTRING(SUBSTRING(val,CHARINDEX('-',val) + 1,LEN(val)),1,LEN(SUBSTRING(val,CHARINDEX('-',val) + 1,LEN(val)))) 'rightVal'
FROM test WHERE ISNUMERIC(val) <= 0
)
SELECT id,val , (CAST(leftVal AS DECIMAL(10,2)) + CAST(rightVal AS DECIMAL(10,2))) / 2 'Ver' FROM t2
create function [dbo].[Get_Number] (@S varchar(100))
returns int
AS
begin
while PATINDEX('%[^0-9]%',@S)>0
begin
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
end
return cast(@S as int)
end
- 无法修改表。
从数据类型 nvarchar 转换为 numeric 时出错。
如何找到不能转换为数值的记录呢 如果是c# 有decimal.tryparse() 这种函数判断是否能转换
SqlServer下是什么