Getting a recordset from a db field that is comma

Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Getting a recordset from a db field that is comma

  1. #1
    Join Date
    Dec 1969
    Posts
    13

    Default Getting a recordset from a db field that is comma

    I Have a field in a DB that contains multiple values:<BR>e.g. (1,2,3) , (1,3,4) or (2,3,4)<BR>I want to write a SQL statement that pulls all recordsets from the DB where a 2 exists in that field. In this case, that would be the 1st and 3rd records.<BR>I havent dealt with fields that contain more than one value and I&#039;m stumped. Any help would be great!

  2. #2
    Join Date
    Dec 1969
    Posts
    136

    Default RE: Getting a recordset from a db field that is co

    sql = "SELECT * FROM whateverTable WHERE whateverField LIKE &#039;%2%&#039;

  3. #3
    Join Date
    Dec 1969
    Location
    Los Angeles, CA
    Posts
    21,192

    Default VERY crappy design

    i suggest you actually fix that instead<BR><BR><BR>anywho try <BR>where CHARINDEX(&#039;,2,&#039;, ColumnName) &#062; 0<BR><BR><BR>

  4. #4
    Join Date
    Dec 1969
    Location
    Los Angeles, CA
    Posts
    21,192

    Default eh!! crappy de doo

    Better way then i did, though i cheked for the , in front and back the actually data dosent seem to have it so my query would not work either....how sad am i<BR><BR><BR>anywho, i think the data should have that also otherwise if the data goes more than 11 (namely 12 or anything with 2) this query will not work either<BR><BR>


  5. #5
    Join Date
    Dec 1969
    Posts
    96,118

    Default Nope...wrong answer...

    Suppose the whateverField contains<BR> 123,204,512<BR><BR>Doing <BR> WHERE whateverField LIKE &#039;%2%&#039;<BR>would find all three of those, when it shouldn&#039;t find any of them.<BR><BR>

  6. #6
    Join Date
    Dec 1969
    Location
    Los Angeles, CA
    Posts
    21,192

    Default See the strange part

    is it WILL contain all that and that is a RIGHT choice, since it has multiple IDs (or whatever) it should return the entire row <BR><BR>i guess what you should have said was if records have 123 or 204 or 512....blah<BR><BR><BR>

  7. #7
    Join Date
    Dec 1969
    Posts
    96,118

    Default Nope, still wrong answer...

    If the columnName field is either<BR> 2,7,43<BR>or<BR> 1,2<BR>or even just<BR> 2<BR>that will *NOT* find it, because it insists on seeing the common on both sides of the number.<BR><BR> WHERE (&#039;,&#039; + columnName + &#039;,&#039;) LIKE &#039;%,2,%&#039;<BR><BR>But of course Atrax is correct: The *RIGHT* answer is to never never never use a delimited field in a database. <BR><BR>

  8. #8
    Join Date
    Dec 1969
    Location
    Los Angeles, CA
    Posts
    21,192

    Default Slow arnt ya old man

    i already said that<BR><BR>&#060;sneer&#062;<BR><BR><BR><BR><BR>

  9. #9
    Join Date
    Dec 1969
    Posts
    136

    Default RE: Nope...wrong answer...

    in the start of his thread he said pull a field wherever a 2 exists, so i gave him that sql.

  10. #10
    Join Date
    Dec 1969
    Posts
    13

    Default RE: Getting a recordset from a db field that is co

    Well, just in case, I changed from a numeric value to a string value, to avoid the 123, 502, 234 issue when looking for a two - or whatever value I&#039;d be looking for. The value was just an ID for a string value anyway.(I know I should be reworking the code to remove the &#039;,&#039; - but the SQL statement given seems to do the trick.<BR>Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •