Position of a group

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Position of a group

Post 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
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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.
Post Reply