need help on like keyword

[habneh]

I have a table say

tbl

col1 col2
1 aa
2 aabc
3 bb
4 bbkk
5 cc
6 dd

I want to select all records from tbl1 excluding records that have col2 values starting aa and bb

therefore my result set should look like

col1 col2
5 cc
6 dd

thanks

 

[SQLBill]

Have you tried:

WHERE COL2 NOT LIKE 'aa%'
  OR COL2 NOT LIKE 'bb%'

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   tbl
[COLOR=blue]Where[/color]  col2 Not Like [COLOR=red]'aa%'[/color]
       And Col2 Not Like [COLOR=red]'bb%'[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[habneh]

Thank you all

George's post works perfect

 

[bborissov]

With your data you could try this:

[COLOR=blue]declare[/color] @test [COLOR=blue]table[/color] (col1 [COLOR=blue]int[/color], col2 [COLOR=blue]varchar[/color](20))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (1,[COLOR=red]'aa'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (2,[COLOR=red]'aabc'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (3,[COLOR=red]'bb'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (4,[COLOR=red]'bbkk'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (5,[COLOR=red]'cc'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (6,[COLOR=red]'dd'[/color])

[COLOR=blue]SELECT[/color] * [COLOR=blue]from[/color] @test [COLOR=blue]WHERE[/color] col2 NOT LIKE [COLOR=red]'[ab][ab]%'[/color]

BUT this will not works in any cases
If you have data like this:

[COLOR=blue]declare[/color] @test [COLOR=blue]table[/color] (col1 [COLOR=blue]int[/color], col2 [COLOR=blue]varchar[/color](20))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (1,[COLOR=red]'aa'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (2,[COLOR=red]'aabc'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (3,[COLOR=red]'bb'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (4,[COLOR=red]'bbkk'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (5,[COLOR=red]'cc'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (6,[COLOR=red]'dd'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test 
-- Here the problems begins :-)
[COLOR=blue]VALUES[/color] (7,[COLOR=red]'ab'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (8,[COLOR=red]'aba'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test [COLOR=blue]VALUES[/color] (9,[COLOR=red]'bab'[/color])

[COLOR=blue]SELECT[/color] * [COLOR=blue]from[/color] @test [COLOR=blue]WHERE[/color] col2 NOT LIKE [COLOR=red]'[ab][ab]%'[/color]

You'll get the same result as previous query, so SQLBill's and George's answers are more generic.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

Borislav this should work for you

SELECT * from @test WHERE col2 LIKE '[^aa][^bb]%'
OR col2 LIKE '[^bb][^aa]%'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[bborissov]

Denis,
I just wanted to make it with one LIKE , but can't figured out how

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[earthandfire]

Instead of LIKE, this works with Borislav's data:

[tt]SELECT * from @test WHERE Left(col2, 2) NOT IN ('aa', 'bb')[/tt]