求请:查询字段中字符串'0111',或'1111'中有'1'的个数? ( 积分: 20 )

K

kitty_li

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-08

• #1

您好!
数据库存中某一字段由'0',或'1'组成的四位数字符串 如'0111',或'1111'中有几个'1'?
要求不计字符串中第二位(字符串中第二位为'1'不算)如:
1111为3个
1011为3个
0111为2个
用sql查询字段中有'1'的个数?
谢谢!

 

S

stargazer

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-08

• #2

干吗要把数据库设计成这样？

 

C

cnzzlp

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-08

• #3

strSource := Edit1.Text;
// iCount := 0;
for i:=1 to Length(strSource) do
begin
if (strSource = '1')and (i<>2) then
iCount := iCount +1;

end;

 

K

kitty_li

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-08

• #4

请教cnzzlp:
首先谢谢您!
除了用循环,用SQL中统计函数可以实现吗?

 

W

wisenow

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-08

• #5

(substr(字段,1,1)+substr(字段,3,1)+substr(字段,4,1)) as COUNT1

 

K

kitty_li

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-08

• #6

(substr(字段,1,1)+substr(字段,3,1)+substr(字段,4,1)) as COUNT1 这样是字符串相加
不是求字符串中1的个数呀

 

迷

迷途的羔羊

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-08

• #7

select len(REPLACE(STUFF(字段,2,1,''),'0','')) from 表名

 

K

kitty_li

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-08

• #8

谢谢!

 

M

msglzcx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-03-08

• #9

select len( replace (substring('字段',1,1)+substring('字段',3,len('字段')),0,''))