Post reply

how to search in sql by using like operator

  • July 16, 2009 at 7:25 am

    #1025621

    Excellent. no problem 😀

  • July 16, 2009 at 7:26 am

    #1025623

    Ohhhh!!!!!!!!!!

    Excellent it is working.I was mistaked to impliment your code.

    Its woking fine.

    a small thing in it .

    I give parameter :Goo

    it display the good morning data and good data .

    is there to display only 'Goo' data instead of that.

    Thanks
    Dastagiri.D

  • July 19, 2009 at 7:59 pm

    #1026680

    Doylie (7/15/2009)

    .....ignore the bits in the comments that say "comma". they are meant to say "space". I have stolen this code from somewhere else and missed a few bits.

    This will work for any identifyer though so its pretty useful.

    Thanks

    I appreciate your good will but you need to give that code back to where you have stolen the code from. 😛 There's no reason for the While Loops, Doylie. I'll be back in a minute with an example of what I mean. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 19, 2009 at 8:29 pm

    #1026684

    Here's what I'm talking about... the details are in the comments in the code below...

    --===== Create and populate a test table to demo with.

    -- THIS IS NOT PART OF THE SOLUTION!

    DECLARE @TestTable TABLE

    (ID INT, Name VARCHAR(100))

     

    INSERT INTO @TestTable

    (ID, Name)

    SELECT 1,'Good Morning' UNION ALL

    SELECT 2,'Nothing here' UNION ALL

    SELECT 3,'Morning' UNION ALL

    SELECT 4,'Good' UNION ALL

    SELECT 5,'Good Day' UNION ALL

    SELECT 6,'A great morning' UNION ALL

    SELECT 7,'A not so good day' UNION ALL

    SELECT 8,'Nothing here to morn'

     

    --===== This variable simulates the passed in parameter.

    DECLARE @Parameter VARCHAR(100)

    SELECT @Parameter = 'Good Morning'

     

    --===== Split the words in the parameter and find them in the test table

    ;WITH cteSplit AS

    (

    SELECT SUBSTRING(' '+@Parameter, t.N+1, CHARINDEX(' ', @Parameter+' ', t.N) -t.N) AS Word

    FROM dbo.Tally t

    WHERE t.N <= LEN(@Parameter)

    AND SUBSTRING(' '+@Parameter,t.N,1) = ' '

    )

    SELECT DISTINCT tt.*

    FROM @TestTable tt

    INNER JOIN cteSplit s

    ON tt.Name LIKE '%'+s.Word+'%'

    ORDER BY tt.ID

    Notice how short the Tally table makes the code. And, it's fast, too. If you don't know what a "Tally" table is or how it works to replace While Loops in a high speed fashion, please see the following article...

    http://www.sqlservercentral.com/articles/T-SQL/62867/

    If you want to see how to split a whole bunch of different things that look like 1, 2, or 3 dimensional arrays, then please see the following article...

    http://www.sqlservercentral.com/articles/T-SQL/63003/

    As a side bar, yes, you can make a CTE that looks like a Tally table but I keep an 11,000 row Tally table handy in my UTIL DB for doing splits on VARCHAR(8000) and for doing quite a bit of date math and other things you can do with a Tally table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 20, 2009 at 2:57 am

    #1026809

    Impressive jeff, thanks for this.

    I knew you would be able to do this with a CTE, but i have not quite managed to get my head around them yet. I have read both those articles before, and understand them. i just struggle when writing them from scratch when i have to figure out the logic.

  • July 20, 2009 at 6:36 am

    #1026897

    Just to be sure... the "Magic" isn't in the CTE... it's in the Tally table and can be used in SQL Server 2000 just as effectively.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply