Help, query question about "finding a string"

[lok1234]

Dear all,
I have a table (TableA) in Access looks like that:

  STockID       Number_List   
  -------       -----------
  D001          44,45,47,41,42
  D002          1,2,3,5,58,60,61
  D003          49,50,51,48,53,54

P.S. Both fields are having "Text" datatype.

Now, I have to write SQL query which pass a number and find out the STockID, it is something like:

  Select STockID from TableA
   where 51 in (number_list)

P.S. 51 is the value that pass to the SQL query

In the above example, the stockID that returns would be D003.

Hope you understand what I am asking, thanks.

 

[Kiwiman]

Howzit

Try...

Select STockID from TableA
   where (((TableA.number_list) like "*51*"))

As you are not looking for the exact string you will need like, with the * being the wildcards. I have put them on both sides fo 51 as you may not know where 51 is in the filed.

 

[lok1234]

Howzit

Try...

Select STockID from TableA
   where (((TableA.number_list) like "*51*"))

As you are not looking for the exact string you will need like, with the * being the wildcards. I have put them on both sides fo 51 as you may not know where 51 is in the filed.

thanks
but in case there is another number, say 551, in another Number_List record, it might cause selection error:

STockID    Number_List
----------   ----------------
D001         1,5,57,299,551
D002         2

In the above case, actually, "51" does not exist in either STockID.
But If i use where (((TableA.number_list) like "*51*"))
It will select D001 out which is in-correct.

In case I write the where clause in this way:
where (((TableA.number_list) like "*,51,*"))
Then, in case the 51 is in the last position of the string. e.g. 1,5,49,51
it will not be selected out.

Thanks

 

[Kiwiman]

Howzit

You will also need to consider when 51 comes at the beginning of the string.

The like "*,51,*" will not work

51,21,54

You will need to put in an OR stmt

OR ((TableA.number_list) like "51,*") OR ((TableA.number_list) = "51")

 

[just.a.guy]

Here is another option

where instr(","+Table.numberList+',", ',51,') > 0

I add a beginning comma and an ending comma
and then search for comma 51 comma.

This assumes there are no blanks in the list.

If you have blanks use

instr( ","+replace(numberList," "', ""')+",", ",51,") > 0

 

[Atomic Shrimp]

Are the numbers in the number list separate values stored in the same field? If so, that's usually not a good idea, as it means you'll always be writing custom code to slice out or handle the number value you want (as in fact is happening here in this thread) - and you'll always be facing problems with small strings appearing as substrings of longer parts (the 51 vs 551 thing).

Wouldn't it be better to store the number lists in a table with each number on a distinct row? - i.e:

STockID       Number   
  -------       -----------
  D001          44
  D001          45
  D001          47
  D001          41
  D001          42
  D002          1
  D002          2
  D002          3
  D002          5
  D002          58
  D002          60
  D002          61
  D003          49
  D003          50
  D003          51
  D003          48
  D003          53
  D003          54

 

[anski]

hi Atomic Shrimp. i have the same table structure as lok1234 and i want to split the field into multiple rows (just like what you showed). how can i do it without any complicated programming language? i am not knowledgeable with VBA but i can work with queries. thanks.