SQL Teaser Percent

[AlexCuse]

Since everyone is bored on Friday, here is one that someone may find useful (lifted from Red Gate's SQL Stumpers e-book).

Take this sample data:

declare @t table (col varchar(100))

insert @t
select 'this column shows 125% of something'
union all select 'this column shows 55% of something'
union all select 'this column shows 5% of something'
union all select '550% is a lot'
union all select '375%'
union all select '5% is shown in this column'
union all select '1035%'
union all select 'what about 505%'
union all select '5%'
union all select 'ten percent looks like 10%'
union all select 'Why would you care to see 5%'
union all select 'I guess it is better than 1%'

How would you limit your select to only the rows containing 5%?

Enjoy!

Alex



Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

ps you want to exclude things like 1035%

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

documented in LIKE in BOL

Spoiler

Symbol Meaning
LIKE '5[%]' 5%

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

 

[SQLDenis]

didn't see your 2nd request, you want only these 4 rows back right?



union all select 'this column shows 5% of something'
union all select '5% is shown in this column'
union all select '5%'
union all select 'Why would you care to see 5%'

yes?

then this, might have fat-fingerred a space

Spoiler

select * from @t where col LIKE '% 5[%]%'
OR col LIKE '5[%]%'

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

 

[ptheriault]

Like this...

Spoiler

SELECT * FROM @t
where col like '5[%]'

I remember this from a question that George answered not to long ago.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[monksnake]

Here you go, legitimate answer:

Spoiler

select col from @t where charindex(' 5%', col) > 0 or charindex('5%', col) = 1

<.

 

[monksnake]

uh oh Paul, I think you'd better look at that again.

<.

 

[monksnake]

I never get these the 'cool' way.

<.

 

[gmmastros]

Here's another one.

Spoiler

Select *
From @t
Where (' ' + col) like '%[^0-9][5][%]%'

-George

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

 

[ptheriault]

oops.

Spoiler

SELECT * FROM @t
where CHARINDEX (' 5%',col) > 0

Is that better.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

One last change. I need to pay more attention to the data.

Spoiler

SELECT * FROM @t
where (CHARINDEX (' 5%',col) > 0)
or (col = '5%')

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[monksnake]

Is that better.

Almost.

You'll get this returned

'what about 505%'

<.

 

[AlexCuse]

I need to pay more attention to the data.

Somewhere, Paul's boss just started having a panic attack.

George is closest to what I had (and knowing George, probably better ;-) ):

Spoiler

select col from @t
where patindex('%[^0-9]5[%]%', ' ' + col)>0

Ignorance of certain subjects is a great part of wisdom

 

[monksnake]

I think you are still missing one thing Paul.

Your code still won't get this one.

'5% is shown in this column'


<.

 

[ptheriault]

>>Somewhere, Paul's boss just started having a panic attack.

That's why I don't let my boss anywhere near the db.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[Lamprey13]

Spoiler

SELECT *
FROM @t
WHERE col LIKE '% 5[%]%'
OR col LIKE '5[%]%'

 

[Lamprey13]

Ops, I did missed Senis's answer. Appoligies for the dup.

 

[AlexCuse]

I will keep my finger off the red flag button this time Lamprey13 ;-).

In all seriousness, these things are here for everyone to have fun, and maybe even learn a little bit. No need to apologize!



Ignorance of certain subjects is a great part of wisdom