Find the brackets

  • Comments posted to this topic are about the item Find the brackets

  • IMHO, cleaner code if you take the shortcut.

    SELECT mychar FROM mydata WHERE mychar LIKE '[[]%'

    --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)

  • I tried this against a SQL Server 2008 R2 database and both 1 and 5 brought back the bracketed records. Luckily I choose answer 1.

  • TimCarrett (11/17/2016)


    I tried this against a SQL Server 2008 R2 database and both 1 and 5 brought back the bracketed records. Luckily I choose answer 1.

    Query 5 will bring back records that have characters before the bracket too. The question was for rows that begin with a bracket. Although in the example posted it brings back the same rows, if you run the queries against the table below the outcome will be different.

    CREATE TABLE #MyData

    (

    mychar VARCHAR(50)

    );

    GO

    INSERT #MyData

    (mychar)

    VALUES

    ('This is a string'),

    ('"A Quoted String"'),

    ('''Single quoted string'''),

    ('''more single quotes'''),

    ('AA[My bracketed string]'),

    (''),

    ('[Can I find this string]')

    ;

    GO


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    โ€”Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Wasn't aware of ESCAPE as I would have used '[[]%'

    - Damian

  • Hi Steve,

    it seems to me that there are a couple of minor mistakes in the solution: "The only query that will return the two rows is the one that uses the ESCAPE option ".

    The rows returned are three, not two. Then I think that it would be better to say that the query n.5 returns the three rows as well, but the logic is not correct: in a bigger table it would return also strings like "xyq[My...", "1[Can..." because of the "%".

    Grasshoppers like me could be confused ๐Ÿ™‚

  • I'm assuming there was a typo in one of the options and it was meant to have a character before the initial bracket, so the correct answer would have returned 2 rows and the final option would have been clearly wrong as it returned 3.

    Personally I'd use one of the alternatives suggested in the explanation as I think it's clearer

    SELECT mychar FROM mydata WHERE mychar LIKE '![%' ESCAPE '!'

  • This was removed by the editor as SPAM

  • Nice question on one of the basics of strings. Thanks, Steve.

  • This would also work and keep the code simple...

    SELECT *

    FROM dbo.MyData

    WHERE Left(mychar,1)='[';

  • Laurie Dunn (11/17/2016)


    This would also work and keep the code simple...

    SELECT *

    FROM dbo.MyData

    WHERE Left(mychar,1)='[';

    Yeah I got

    select * from MyData where CHARINDEX('[',mychar,0) = 1

    which works fine, then looked at the options and realised today's lesson was about ESCAPE :hehe:

  • Jeff Moden (11/16/2016)


    IMHO, cleaner code if you take the shortcut.

    SELECT mychar FROM mydata WHERE mychar LIKE '[[]%'

    That's how I went about doing it and then had to take a closer look at the possible answers ๐Ÿ™‚

    Cheers

  • funbi (11/17/2016)


    Laurie Dunn (11/17/2016)


    This would also work and keep the code simple...

    SELECT *

    FROM dbo.MyData

    WHERE Left(mychar,1)='[';

    Yeah I got

    select * from MyData where CHARINDEX('[',mychar,0) = 1

    which works fine, then looked at the options and realised today's lesson was about ESCAPE :hehe:

    Both of these queries are non SARGable, which would result in an index scan instead of an index seek.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (11/17/2016)


    funbi (11/17/2016)


    Laurie Dunn (11/17/2016)


    This would also work and keep the code simple...

    SELECT *

    FROM dbo.MyData

    WHERE Left(mychar,1)='[';

    Yeah I got

    select * from MyData where CHARINDEX('[',mychar,0) = 1

    which works fine, then looked at the options and realised today's lesson was about ESCAPE :hehe:

    Both of these queries are non SARGable, which would result in an index scan instead of an index seek.

    The table has 7 rows.

  • This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 40 total)

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