locked
Pattern check in sql RRS feed

  • Question

  • I need to select records from a table which doesnt follow a pattern
    pattern 1 : @ and Pattern 2:  BA_

    Table:User
    column_name
    King@
    Kings
    LeBA_On
    Fig@onBA_
    BA_int


    My query shold return only one record fron the ablove table and it shold be "KIngs"
    I need to fecth the names which doesnt have  @ and BA_

    I tried the following query but it doenst work

    Select column_name from user where column_name not like '%[@]%' and  column_name not like '%[BA_]%'

    Thursday, July 21, 2011 3:55 AM

Answers

  • You were very close. Try:

    CREATE TABLE TestTable (Col1 varchar(10));
    
    INSERT INTO TestTable 
    VALUES ('King@'),
     ('Kings'),
     ('LeBA_On'),
     ('Fig@onBA_'),
     ('BA_int'),
     ('BAT');
    
    SELECT *
    	FROM TestTable
    	WHERE Col1 NOT LIKE <a href="mailto:'%@%'" rel="nofollow">'%@%'</a>
    	AND Col1 NOT LIKE '%BA\_%' ESCAPE '\'
    

    The ESCAPE is needed to treat the underscore as a regular character instead of a wild card.

    HTH


    Vern Rabe
    • Proposed as answer by Rishabh K Thursday, July 21, 2011 4:17 AM
    • Marked as answer by Cool Tech Thursday, July 21, 2011 4:22 AM
    Thursday, July 21, 2011 4:09 AM
  • Try:

    use TestN 
    CREATE TABLE TestTable (Col1 varchar(10));
    
    INSERT INTO TestTable 
    VALUES ('King@'),
     ('Kings'),
     ('LeBA_On'),
     ('Fig@onBA_'),
     ('BA_int'),
     ('BAT'),
     ('KingsBaon_');
     
     select * from TestTable where Col1 NOT LIKE '%@%' and Col1 NOT LIKE '%BA[_]%'
    
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Cool Tech Thursday, July 21, 2011 4:37 AM
    Thursday, July 21, 2011 4:19 AM

All replies

  • Hi,

    try this:

    declare @pattern table(col varchar(10))
    insert into @pattern
    select '@' union all
    select 'BA_'

     

    select column_name from [user]
    where not exists(select col from @pattern
    where column_name  like '%'+col+'%')


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Thursday, July 21, 2011 4:02 AM
  • You were very close. Try:

    CREATE TABLE TestTable (Col1 varchar(10));
    
    INSERT INTO TestTable 
    VALUES ('King@'),
     ('Kings'),
     ('LeBA_On'),
     ('Fig@onBA_'),
     ('BA_int'),
     ('BAT');
    
    SELECT *
    	FROM TestTable
    	WHERE Col1 NOT LIKE <a href="mailto:'%@%'" rel="nofollow">'%@%'</a>
    	AND Col1 NOT LIKE '%BA\_%' ESCAPE '\'
    

    The ESCAPE is needed to treat the underscore as a regular character instead of a wild card.

    HTH


    Vern Rabe
    • Proposed as answer by Rishabh K Thursday, July 21, 2011 4:17 AM
    • Marked as answer by Cool Tech Thursday, July 21, 2011 4:22 AM
    Thursday, July 21, 2011 4:09 AM
  • hi ,thanks for the reply.

    But this is not working for the name eg  KingBaone_ .This is a valid one

    Thursday, July 21, 2011 4:10 AM
  • Rishabh's solution didn't deal with the underscore as a wild card. Try my solution
    Vern Rabe
    Thursday, July 21, 2011 4:13 AM
  • I got this to work...

     

    -- Test Data --
    DECLARE @User TABLE (column_name VarChar(100))
    INSERT INTO @User Values ('King@'),('Kings'),
    ('LeBA_On'),('Fig@onBA_'),('BA_int')
    
    -- Solution --
    SELECT column_name
    FROM @User
    WHERE CHARINDEX('BA_', column_name) + CHARINDEX('@', column_name) = 0
    


     


    Jason Long
    Thursday, July 21, 2011 4:15 AM
  • Try:

    use TestN 
    CREATE TABLE TestTable (Col1 varchar(10));
    
    INSERT INTO TestTable 
    VALUES ('King@'),
     ('Kings'),
     ('LeBA_On'),
     ('Fig@onBA_'),
     ('BA_int'),
     ('BAT'),
     ('KingsBaon_');
     
     select * from TestTable where Col1 NOT LIKE '%@%' and Col1 NOT LIKE '%BA[_]%'
    
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Cool Tech Thursday, July 21, 2011 4:37 AM
    Thursday, July 21, 2011 4:19 AM