Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2003
    Posts
    232

    Arrow Unanswered: simple excluding of character query

    i have a field with 2 characters(many of them ) and spaced inbetween them
    which are codes for something


    ie XA OX YY BY CY DY XC XD OE In any or all combination

    i want to make a count of rows which contains BOTH XA
    BUT DOES NOT CONTAIN any one or all of XC,XD & OE

    SO I write the query
    select count(colname)
    from tablename
    where colname LIKE ('%XA%')
    AND colname NOT LIKE ('%XC%')
    OR colname NOT LIKE ('%XD%')
    OR colname NOT LIKE ('%OE%')
    ')

    iS this correct
    why then it does not exclude the XC,XD,OE

    is there a better way ?
    bigfoots

  2. #2
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    select count(colname)
    from tablename
    where colname LIKE ('%XA%')
    AND (colname NOT LIKE ('%XC%')
    OR colname NOT LIKE ('%XD%')
    OR colname NOT LIKE ('%OE%')
    )
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    CHARINDEX might be faster than LIKE:

    Code:
    where	charindex('XA', colname) > 0
    	AND charindex('XC', colname) = 0
    	AND charindex('XD', colname) = 0
    	AND charindex('OE', colname) = 0
    Try it and see.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2003
    Posts
    232
    does not work i tried both



    there are spaces between the XA etc
    bigfoots

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dude!

    Code:
    select count(colname)
      from tablename o
     where EXISTS (	    SELECT * FROM tablename i1 
    		     WHERE colname LIKE ('%XA%')
    		       AND o.key = i1.key)
       and NOT EXISTS(  SELECT * FROM tablename t2 
    		     WHERE ( colname LIKE ('%XC%')
    			OR   colname LIKE ('%XD%')
    			OR   colname LIKE ('%OE%'))
    		       AND o.key = i2.key)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Oct 2003
    Posts
    232
    i expressed it wrong i thing

    One column in my table contains variable with spaces
    ie XA OX YY BY CY DY XC XD OE ----->In any or all combination

    i want to count the rows that has only XA but not XC,XD

    So what should i write ; i taught this would work

    select count(colname)
    from tablename
    where colname LIKE ('%XA%')
    AND (colname NOT LIKE ('%XC%')
    OR colname NOT LIKE ('%XD%')
    OR colname NOT LIKE ('%OE%')
    )
    but does not work
    bigfoots

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you try mine?

    And what happend to OE?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Or mine? 'Cause it works EXACTLY as you requested...

    Code:
    create table #TestTable(TestString varchar(50))
    
    insert into #TestTable(TestString) Values('XA OX YY BY CY DY XC XD OE')  --Bad rec, includes disallowed codes
    insert into #TestTable(TestString) Values('XZ OX YY BY CY DY XK XP OE')  --Bad rec, does not include required code
    insert into #TestTable(TestString) Values('XA OX YY BY CY DY XK XP OQ')  --Good rec
    
    select count(*) as ValidRecords
    from	#TestTable
    where	charindex('XA', TestString) > 0
    	AND charindex('XC', TestString) = 0
    	AND charindex('XD', TestString) = 0
    	AND charindex('OE', TestString) = 0
    
    drop table #TestTable
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    True...very true....

    Unless he did not explain the problem....

    Post sample DDL and DML and Expected results like the blind dude if you need anymore help...

    I'm Gone..5:00 starts 1 hour early today....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Oct 2003
    Posts
    232

    Arrow

    Brett
    i did not understand

    where EXISTS ( SELECT * FROM tablename i1
    WHERE colname LIKE ('%XA%')
    AND o.key = i1.key) <-------- this part
    and NOT EXISTS( SELECT * FROM tablename t2
    WHERE ( colname LIKE ('%XC%')
    OR colname LIKE ('%XD%')
    OR colname LIKE ('%OE%'))
    AND o.key = i2.key) <---------this part

    so can you explain that

    Blindman
    i imported from a text file into a table many column including this column
    where i do a count

    there are many rows about 20,000
    so writing an insert will be pain besides what is changed in the new table that will give me the answer differently?
    Last edited by sjumma; 02-25-05 at 17:22.
    bigfoots

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh.

    My.

    God.

    {sigh}

    The inserts were just part of the code to show you that the solution works, not part of the solution itself.

    And nothing has changed from previous one I sent, because it already worked exactly as you requested, which you would see if you had run the script I sent the SECOND time.

    {aarrgghhh}
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Oct 2003
    Location
    Jordan
    Posts
    28

    Smile

    SELECT Count(TestString) as VCounts FROM TestTable
    WHERE
    TestString Like '%XA%'
    AND
    TestString Not Like '%XC%'
    And
    TestString Not Like '%XD%'
    And
    TestString Not Like '%OE%'
    Firas arramli
    Systems Analyst

  13. #13
    Join Date
    Oct 2003
    Posts
    232
    Blind man

    when i ran your query it returns zero rows possibly it has no rows left ??

    i can send the real file to someone who sends me email

    my address sjumma_2000@yahoo.com
    Last edited by sjumma; 02-28-05 at 17:29.
    bigfoots

  14. #14
    Join Date
    Oct 2003
    Posts
    232

    Arrow

    no one still
    bigfoots

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    One last time, sjumma.

    The code I posted above creates a sample table, loads some sample data into it, runs the logic you specified, and gives the results you requested. If the structure or fields of the sample table I gave you are significantly different than the table you are using, then you will need to explain what is different, because we have already proved several times that the code we have given you works.

    If that logic applied to your table is not returning any records, then either you have not explained your problem correctly, or there is a problem with your data.

    I suggest that you start by simplifying your criteria. First, see if you can get a count of all the records that contain 'XA'. Once you can do this succesfully, add more clauses to eliminate records containing disallowed character combinations.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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