what is mean by '' ? + in where clause

Question

Post reply

what is mean by '' ? + in where clause

karthik M

One Orange Chip

Points: 29139
More actions
December 11, 2008 at 2:28 am

#73157

Hi All,

I want to know one basic thing...yes...what is the internal thing happening while executing the below query ?

Create table emp

(

eno int,

ename varchar(25)

)

insert into emp

select 1,''

union

select 2,'kk'

union

select 3,'qq'

select * from emp

where name > ''

Actually it neglect the blank space and fetch the records. am i right ?

output:

2 kk

3 qq

how it is working internally ?

Inputs are welcome!

karthik

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

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

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 1

Madhivanan-208264

SSCertifiable

Points: 7516

December 11, 2008 at 7:22 am

#911057

Yes. It will also omit NULLs

Madhivanan

Failing to plan is Planning to fail

Jerry Hung SSChampion Points: 12908 More actions · Answer 2

Jerry Hung

SSChampion

Points: 12908

December 11, 2008 at 7:44 am

#911085

I always assume it means empty string

'' = EMPTY (not the same as NULL)

' ' = 1 space

SQLServerNewbie MCITP: Database Administrator SQL Server 2005

karthik M One Orange Chip Points: 29139 More actions · Answer 3

karthik M

One Orange Chip

Points: 29139

December 12, 2008 at 4:59 am

#911565

Any more inputs ?

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

karthikeyan (12/12/2008)

Any more inputs ?

What do you want to know?

Why should it be any different to

select * from emp where name > 'Karthik'?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

karthik M One Orange Chip Points: 29139 More actions · Answer 5

Chris,

I have tested your scenario.

create table #t1

(

name varchar(15)

)

go

insert into #t1

select 'karthik'

union

select 'kuhan'

union

select 'kamatchi'

union

select 'Guna'

union

select 'ravi'

query:

select * from #t1

where name > 'karthik'

output:

ravi

kuhan

why it is not showing the remaining names ? i.e Guna & kamatchi

again i have inserted one more row.

insert into #t1

select 'Jonny'

now i executed the above query once again, it gave me the same result set.

output:

ravi

kuhan

Chris,

can you explain me what is happening ? i am very much confused....

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Karthik, pick up a telephone directory sometime. Here's a hint or two:

DROP TABLE #t1



create table #t1 (name varchar(15))

insert into #t1

select 'Guna' union all

select 'Jonny' union all

select 'kamatchi' union all

select 'karthik' union all

select 'kuhan' union all

select 'ravi' 

select * from #t1 where name > 'Guna' ORDER BY name

select * from #t1 where name > 'karthik' ORDER BY name

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

karthik M One Orange Chip Points: 29139 More actions · Answer 7

karthik M

One Orange Chip

Points: 29139

December 12, 2008 at 7:06 am

#911609

ok.

but if we have unordered data...what will happen ?

you sample data has ordered, so we no need to worry about the result...

i think we can use ORDERBY clause...but again if we have more than 7000000, i am sure it leads to performance issue.

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

karthikeyan (12/12/2008)

ok.

but if we have unordered data...what will happen ?

you sample data has ordered, so we no need to worry about the result...

i think we can use ORDERBY clause...but again if we have more than 7000000, i am sure it leads to performance issue.

Try it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

karthik M One Orange Chip Points: 29139 More actions · Answer 9

karthik M

One Orange Chip

Points: 29139

December 12, 2008 at 7:12 am

#911613

yes...i have tried your code...

the result is like as below

#1:

Jonny

kamatchi

karthik

kuhan

ravi

#2:

kuhan

ravi

but if i don't have index...or if we dont have ordered result set....what will happen? it leads to wrong result set. Am i right ?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 10

karthik M

One Orange Chip

Points: 29139

December 12, 2008 at 7:13 am

#911615

Basically

where name > ''

should avoid balnk string values. Am i right ? how it will check the datapages internally? thats what i wanted to know...

karthik

betsy.powlen SSC Enthusiast Points: 111 More actions · Answer 11

The query is not pulling by latest insert, it's pulling alphabetically (higher ascii number?), so when you ask it to find > 'karthik', it's probably looking at the ascii equivilant for the name and pulling the ones that have a higher ascii number than karthik. At least that's what I think it's doing, someone correct me if this is not the case ;). When I added Zena, that came up, but not Anna.

Vic.K SSC Eights! Points: 910 More actions · Answer 12

Vic.K

SSC Eights!

Points: 910

December 12, 2008 at 7:17 am

#911620

karthikeyan (12/12/2008)

but if i don't have index...or if we dont have ordered result set....what will happen? it leads to wrong result set. Am i right ?

Try this:



IF 'b' > 'a'

  Select 'bingo, it works!'

Else

  Select 'that can''t be true!'

Garadin One Orange Chip Points: 29613 More actions · Answer 13

karthikeyan (12/12/2008)

yes...i have tried your code...

the result is like as below

#1:

Jonny

kamatchi

karthik

kuhan

ravi

#2:

kuhan

ravi

but if i don't have index...or if we dont have ordered result set....what will happen? it leads to wrong result set. Am i right ?

Why ask this question? Why not simply unorder the result set and run it again? It would have taken less time to test it yourself and see that you were wrong than it did to type this out.

See the following:

select 'aaazzzz' v

into #1 union all

select 'bbb' union all

select 'zzz' union all

select 'ccc' union all

select 'b' union all

select 'a' union all

select 'bb'

select * from #1 where v > 'b'

drop table #1

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

karthik M One Orange Chip Points: 29139 More actions · Answer 14

karthik M

One Orange Chip

Points: 29139

December 12, 2008 at 7:21 am

#911624

yes...it should work..

because it should use the ascii value..so ascii value for b is always greater than ascii value of a...so it will show the first part...

but in my case....

what is the ascii value for 'karthik' ? because it has 7 characters...how it will calculate it internally ?

karthik