add string WHERE filter that will match everything

Question

add string WHERE filter that will match everything

KoldCoffee

SSCoach

Points: 18971
More actions
November 19, 2014 at 3:37 pm

#298827

I need to add a filter clause like
WHERE username = '%%%'
It needs NOT to filter anything out.
I know you will say 'why add a filter if you're not going to use it?' but I need to for a certain application which will use the parent query for child queries in which I select the specificity required for the child query's data set.
I've tried '%*%' and '%_%' but always it returns nothing. I need the filter to exist yet not really filter.
Is this possible?

Viewing 7 posts - 1 through 6 (of 6 total)

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

ScottPletcher SSC Guru Points: 101099 More actions · Answer 1

Try "LIKE" '%' rather than "=" :-).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

Even that option could filter something.

DECLARE @test-2 TABLE(

string varchar(30)

)

INSERT @test-2 VALUES ('dasfsa'),('%!·$%/'), (''), (NULL)

SELECT *

FROM @test-2

WHERE string LIKE '%'

SELECT *

FROM @test-2

--What about this?

SELECT *

FROM @test-2

WHERE 1=1

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

KoldCoffee SSCoach Points: 18971 More actions · Answer 3

KoldCoffee

SSCoach

Points: 18971

November 19, 2014 at 3:51 pm

#1759679

...

KoldCoffee SSCoach Points: 18971 More actions · Answer 4

I'm going to use Scott Pletcher's answer....Works for me!

if someone says it's not perfect, can you give more info?

ScottPletcher SSC Guru Points: 101099 More actions · Answer 5

NULL will never be "LIKE" or "=" anything. To allow NULL values, you'd have to add:

WHERE (column_name LIKE '%' OR column_name IS NULL)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

KoldCoffee SSCoach Points: 18971 More actions · Answer 6

KoldCoffee

SSCoach

Points: 18971

November 19, 2014 at 4:39 pm

#1759695

Ah, that is exactly awesome! Thanks Scott!