Y-0.75-4
Y-0.75-4(7043油漆) --注释:变成Y-0.75-4
Y-0.75-4(7043油漆,精车) --注释:变成Y-0.75-4
Y-0.75-4(7043油漆订TH铭牌) --注释:变成Y-0.75-4(注意‘(’前有空格)以下类推
Y-0.75-4(机身客户提供油漆)
Y-0.75-4(键槽居中)
Y-0.75-4(用Y2橡皮垫)
Y-0.75-4(整机精车)
Y-1.1-4
Y-1.1-4(7043油漆)
Y-1.1-4(7043油漆)(整机精车)
Y-1.1-4(7043油漆订TH铭牌)
Y-1.1-4(7043油漆铭牌打TH)
Y-1.1-4(7043油漆铭牌打TH)(整机精车)
Y-1.1-4(订F级)
Y-1.1-4(反装)
Y-1.1-4(精车)
Y-1.1-4(英文订F级)
Y-0.75-4(7043油漆) --注释:变成Y-0.75-4
Y-0.75-4(7043油漆,精车) --注释:变成Y-0.75-4
Y-0.75-4(7043油漆订TH铭牌) --注释:变成Y-0.75-4(注意‘(’前有空格)以下类推
Y-0.75-4(机身客户提供油漆)
Y-0.75-4(键槽居中)
Y-0.75-4(用Y2橡皮垫)
Y-0.75-4(整机精车)
Y-1.1-4
Y-1.1-4(7043油漆)
Y-1.1-4(7043油漆)(整机精车)
Y-1.1-4(7043油漆订TH铭牌)
Y-1.1-4(7043油漆铭牌打TH)
Y-1.1-4(7043油漆铭牌打TH)(整机精车)
Y-1.1-4(订F级)
Y-1.1-4(反装)
Y-1.1-4(精车)
Y-1.1-4(英文订F级)
select substring('Y-0.75-4(7043油漆)',1,charindex('(','Y-0.75-4(7043油漆)')-1)
select substring('Y-0.75-4 (7043油漆)',1,charindex(' (','Y-0.75-4 (7043油漆)')-1)
and fnumber >='5.1.00.2.b3.00025'and t1.fdate between '2010-01-01'and '2010-11-01'
group by t3.fmodel
order by fmodel
仿造你上面改成如下:
select substring(fmodel,1,charindex('(',fmodel)-1),sum(t2.fqty)fqty from seorder t1,seorderentry t2 ,t_icitem t3 where t1.finterid=t2.finterid and t3.fitemid=t2.fitemid
and fnumber >='5.1.00.2.b3.00025'and t1.fdate between '2010-01-01'and '2010-11-01'
group by t3.fmodel出现错误:
服务器: 消息 536,级别 16,状态 3,行 1
向 substring 函数传递了无效的 length 参数。
说明:fmodel就是上面的Y-0.75-4这列,fqty,提问中没写上去
还有“(”,“ (”这两种能否写到一起
IF OBJECT_ID('tb1') IS NOT NULL DROP TABLE tb1
GO
CREATE TABLE tb1
(
col VARCHAR(50)
)
INSERT INTO tb1
SELECT 'Y-0.75-4'
UNION ALL SELECT 'Y-0.75-4(7043油漆)'
UNION ALL SELECT 'Y-0.75-4(7043油漆,精车)'
UNION ALL SELECT 'Y-0.75-4(7043油漆订TH铭牌)'
UNION ALL SELECT 'Y-0.75-4 (机身客户提供油漆)'
UNION ALL SELECT 'Y-0.75-4(键槽居中)'
UNION ALL SELECT 'Y-0.75-4(用Y2橡皮垫)'
UNION ALL SELECT 'Y-0.75-4(整机精车)'
UNION ALL SELECT 'Y-1.1-4'
UNION ALL SELECT 'Y-1.1-4(7043油漆)'
UNION ALL SELECT 'Y-1.1-4(7043油漆)(整机精车)'
UNION ALL SELECT 'Y-1.1-4(7043油漆订TH铭牌)'
UNION ALL SELECT 'Y-1.1-4(7043油漆铭牌打TH)'
UNION ALL SELECT 'Y-1.1-4(7043油漆铭牌打TH)(整机精车)'
UNION ALL SELECT 'Y-1.1-4(订F级)'
UNION ALL SELECT 'Y-1.1-4(反装)'
UNION ALL SELECT 'Y-1.1-4(精车)'
UNION ALL SELECT 'Y-1.1-4(英文订F级'SELECT col= CASE WHEN CHARINDEX('(',col,1)>0 THEN RTRIM(LEFT(col,CHARINDEX('(',col,1)-1)) ELSE RTRIM(col) END FROM tb1
DROP TABLE tb1/*
col
--------------------------------------------------
Y-0.75-4
Y-0.75-4
Y-0.75-4
Y-0.75-4
Y-0.75-4
Y-0.75-4
Y-0.75-4
Y-0.75-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4(所影响的行数为 18 行)*/
select
case
when charindex('(',字段名) > 0 then substring(字段名,1,charindex('(',字段名)-1)
when charindex(' (',字段名) > 0 then substring(字段名,1,charindex(' (',字段名)-1)
else 字段名
end as 字段名
from 表名
INSERT INTO tb
SELECT 'Y-0.75-4'
UNION ALL SELECT 'Y-0.75-4(7043油漆)'
UNION ALL SELECT 'Y-0.75-4(7043油漆,精车)'
UNION ALL SELECT 'Y-0.75-4(7043油漆订TH铭牌)'
UNION ALL SELECT 'Y-0.75-4 (机身客户提供油漆)'
UNION ALL SELECT 'Y-0.75-4(键槽居中)'
UNION ALL SELECT 'Y-0.75-4(用Y2橡皮垫)'
UNION ALL SELECT 'Y-0.75-4(整机精车)'
UNION ALL SELECT 'Y-1.1-4'
UNION ALL SELECT 'Y-1.1-4(7043油漆)'
UNION ALL SELECT 'Y-1.1-4(7043油漆)(整机精车)'
UNION ALL SELECT 'Y-1.1-4(7043油漆订TH铭牌)'
UNION ALL SELECT 'Y-1.1-4(7043油漆铭牌打TH)'
UNION ALL SELECT 'Y-1.1-4(7043油漆铭牌打TH)(整机精车)'
UNION ALL SELECT 'Y-1.1-4(订F级)'
UNION ALL SELECT 'Y-1.1-4(反装)'
UNION ALL SELECT 'Y-1.1-4(精车)'
UNION ALL SELECT 'Y-1.1-4(英文订F级'
goselect case when charindex(' ' , col) = 0 and charindex('(' , col) = 0 then col
when charindex(' ' , col) > 0 and charindex('(' , col) = 0 or
charindex(' ' , col) > 0 and charindex('(' , col) > 0 and charindex(' ' , col) < charindex('(' , col) then left(col,charindex(' ' , col)-1)
when charindex(' ' , col) = 0 and charindex('(' , col) > 0 then left(col,charindex('(' , col)-1)
end col from tbdrop table tb/*
col
--------------------------------------------------
Y-0.75-4
Y-0.75-4
Y-0.75-4
Y-0.75-4
Y-0.75-4
Y-0.75-4
Y-0.75-4
Y-0.75-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4
Y-1.1-4(所影响的行数为 18 行)
*/