Page 1 of 1

Position of a group

Posted: Wed Aug 11, 2004 12:38 am
by anjanesh

Code: Select all

SELECT LOCATE('ggg','aaa,bbb,ccc,ddd,eee,fff,ggg,hhh,iii,jjj')
gives 25
But I want is the postion - 7
How do I get that. I can get it in PHP using explode but it has to be done in the SQL query

Code: Select all

SELECT LOCATE('ggg','aaa,bbb,ccc,ddd,eee,fff,ggg,hhh,iii,jjj')/3-1
will give 7.33 where I can get rid off the .33 but thats taking into assumption that all are 3 chars long.
Thanks

Posted: Wed Aug 11, 2004 1:30 am
by anjanesh
Im trying to do this and almost got it except for one small issue

Take one row as example :

field1 - contains 'aaa,bbb,ccc,ddd,eee,fff,ggg,hhh,iii,jjj'
field2 - contains '1001101001'

Need to search for position of 'ggg' in field1 if it exists. (7 in this case and 0 if not found)
If it does exists then get that position's bit in field2 (7th bit is 1 in this case)
This can be done using :

Code: Select all

SELECT FIND_IN_SET('ggg',field1),SUBSTRING(field2,FIND_IN_SET('ggg',field1),1) FROM `table1`
But I want to check is : if 'ggg' does not exist then FIND_IN_SET('ggg',field1) will return 0 and its no point in continuing with SUBSTRING(field2,FIND_IN_SET('ggg',field1),1)
How am I suppossed to get of that ?
Thanks

Posted: Wed Aug 11, 2004 1:40 am
by feyd
have you tried using an IF?

also, you can use bitwise compares instead of substring..

Code: Select all

field & 1 << FIND_IN_SET('ggg',field1)
that is, if field2 is an integer base type..

Posted: Wed Aug 11, 2004 2:26 am
by anjanesh
Yes. I intend to have field2 to some other base so that I can save space. But I was thinking of base 36 (this is where the prev post was abt) so that I can store large values. field2 is min 50 bits so it can't be INT. If I've to use BIGINT I might as well use VARCHAR and store it as base 36.
Reagrding IF I thought its like in a subselect SELECT...WHERE (SELECT..) which is supported only in 4.1 and above. So I havent tried it yet.

By the way SUBSTRING(field2,FIND_IN_SET('ggg',field1),1) gives NULL if FIND_IN_SET('ggg',field1)=0 so that MAY be one possibility. But Im unsure of it.