sql find exact string, NOT LIKE

[lukaxa]

i have table with two column

ID and groups.


ID: (1) (2) (3)
Groups: (2,12,21) (11,1,31) (43,44,144)


i want to find id with group 1, if i use "Like" , i am receiving every ID with number 1 in groups: 12,21,11,1,31,144.
answer will be (1) (2) (3)


i want just second ID with group number 1

ID (2)

maybe there is some Substring function, and i'm able to separate this values by ","

sorry for bad English

[Rabbit]

You are having so much trouble because your data is not normalized. It's poorly structured. Please read our article on normalization and then restructure your data.
http://bytes.com/topic/access/insigh...ble-structures

[lukaxa]

this is not an option. this data is fixed and given from other person.

how can i normalize data?

there can be 2 group per user or 1000 group per user. i can't create columns for each one.

[Rabbit]

You don't create a column for each one. That's against normalization. You create a row for each id/group member. Given the data above, a normalized structure would have 2 columns and 9 rows. But you can't normalize your data so that's a moot point.

Your only option then is to build a custom parser using VBA code. You won't be able to do it using just SQL alone.

[Basanth]

SELECT * FROM table WHERE GROUPS LIKE '%,1,%'

I think will do the trick...

[Rabbit]

@Basanth, that won't work because it doesn't separate the groups within the the field.