Find the brackets

Question

Post reply

Find the brackets

Steve Jones - SSC Editor

SSC Guru

Points: 736193
More actions
November 16, 2016 at 8:20 pm

#331921

Comments posted to this topic are about the item Find the brackets
November 17, 2016 at 4:49 am

This was removed by the editor as SPAM
November 17, 2016 at 7:42 am

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

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)

TimCarrett Default port Points: 1484 More actions · Answer 2

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.

Neil Burton SSC-Insane Points: 22999 More actions · Answer 3

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

DamianC SSCrazy Eights Points: 8023 More actions · Answer 4

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

- Damian

TheTrojan SSC Eights! Points: 836 More actions · Answer 5

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 🙂

Toreador SSChampion Points: 11365 More actions · Answer 6

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 '!'

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

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

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Laurie Dunn Ten Centuries Points: 1101 More actions · Answer 8

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

SELECT *

FROM dbo.MyData

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

funbi SSCarpal Tunnel Points: 4992 More actions · Answer 9

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:

m mcdonald Hall of Fame Points: 3264 More actions · Answer 10

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

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

funbi SSCarpal Tunnel Points: 4992 More actions · Answer 12

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.