adding substring

[r17]

hello,

i have a table of data filled with 1 and 0  with the help of substring.

 

Jan	Feb	Mar	Apr	May	June	July	Aug	Sept	Oct	Nov	Dec	Total
1	0	1	1	1	1	1	0	1	0	1	1	?????

 

is there a way in which i am able to add up all the 1 (formed by substring) ??

Thanks :D

[slow]

you can do it with java...
it's a fun trick:

suppose $F{TEXT} contains your string...

then you can print the "1" count with a textfield (Integer type)

new Integer($F{TEXT}.replaceAll("[^1]","")).length())

 

 

____________________________________
if it works... give me KARMA points please!    : ) 

[r17]

is there a way in which i can do in SQL?

[slow]

string functions are not SQL standard... so you're talking abot SQL for ORACLE, mySQL, Hypersonic... what?

 

[r17]

im actually trying to transfer from mysql into sql in ireport.

[slow]

in mySql...  try something like this:

SELECT ....... , CHAR_LENGTH(REPLACE( yourField , '0', '')) as length
FROM .....

 

____________________________________
if it works... give me KARMA points please!    : ) 

 

 

 

[r17]

hi slow, thanks for helping me but your answer doesnt apply to mine.

i wanna add up all the 1 together.. is there a function to do so? like sum ( substring )?????????????????

[slow]

if your substring is in the format "011100101001"

then

SELECT  CHAR_LENGTH(REPLACE( substring , '0', '')) as length
FROM aTable

return the sum you want... 6!

 

is the substring you talk about in a different format?

 

____________________________________
if it works... give me KARMA points please!    : ) 

Post Edited by slow at 04/26/2010 14:47

[r17]

this is my sql statment

SELECT*,
campaignmonth, substring(campaignmonth,1,1)as Jan

From campaign;

I'd like to sum up all the substring information as shown in the table above. is there a way to do so?

 

[r17]

Alright, i solved it. thanks !