对字符串中的"+"号进行判断

Question

Create table ZUTAO1(生产单号 varchar(100),
                    规格 varchar(100)
)--insert into ZUTAO1(生产单号,规格)values('Test_001','1左无扶手+右躺')
--insert into ZUTAO1(生产单号,规格)values('Test_001','1左无扶手+右躺')
--insert into ZUTAO1(生产单号,规格)values('Test_002','1左双扶手+右躺')select * from ZUTAO1以上SQL语句得到结果集如下:
生产单号   规格
Test_001 1左无扶手+右躺
Test_001 1左无扶手+右躺
Test_002 1左双扶手+右躺需求:
在"规格"列中,
对"+"号进行判断,当没有出现"+"号,更新为1;
出现一个"+",更新为2;
出现二个"+",更新为3;依此类推
请问该怎么写SQL?

Answer 1

len(规格) -len(replace(规格,'+',''))

Answer 2

update tb
set 规格=len(规格)-len(replace(规格,'+',''))

Answer 3

Create table ZUTAO1(生产单号 varchar(100),
                    规格 varchar(100)
)insert into ZUTAO1(生产单号,规格)values('Test_001','1左无扶手+右躺')
insert into ZUTAO1(生产单号,规格)values('Test_001','1左无扶手+右躺')
insert into ZUTAO1(生产单号,规格)values('Test_002','1左双扶手+右躺')select 生产单号,规格,[数量]=len(规格)-len(replace(规格,'+',''))+1 from ZUTAO1/*
生产单号                                                                                                 规格                                                                                                   数量
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------
Test_001                                                                                             1左无扶手+右躺                                                                                             2
Test_001                                                                                             1左无扶手+右躺                                                                                             2
Test_002                                                                                             1左双扶手+右躺                                                                                             2(3 行受影响)*/drop table ZUTAO1

Answer 4

update tb
set 规格=(len(规格+'a')-len(replace(规格+'a','+','')))%len(规格)+1