Query on empty strings in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query on empty strings in a table

Author

Topic

Bellus
Starting Member

29 Posts

Posted - 2007-10-25 : 05:37:37

Hey

Is it possible to search for a column without a value?

$query="select id from table1 where col2=''"; (this didnt work, but how do I do it??)

I need the id for the row that has the col2 empty.

:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-25 : 05:40:07

is it empty or NULL ?

KH
[spoiler]Time is always against us[/spoiler]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-25 : 05:40:31

[code]Select id from table1 where datalength(col2)=0[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-10-26 : 03:15:13

Select Col1 from Table1 Where (Col2 Is Null or Len(Col2)=0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-26 : 04:19:16

quote:
Originally posted by nirene

Select Col1 from Table1 Where (Col2 Is Null or Len(Col2)=0)

Select Col1 from Table1 Where (Col2 Is Null or Col2='')

Madhivanan

Failing to plan is Planning to fail

Bellus
Starting Member

29 Posts

Posted - 2007-10-26 : 06:04:41

Its empty because when I tried

select ..... where col1=NULL,

I didnt get an error, but just 0 rows.

when I tried

select........where col1=''; I just get an error...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-26 : 06:08:31

quote:
Originally posted by Bellus

Its empty because when I tried

select ..... where col1=NULL,

I didnt get an error, but just 0 rows.

when I tried

select........where col1=''; I just get an error...

What is the error?
Did you read my previous reply?

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-26 : 06:22:01

What is the data type of Col1?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 06:26:00

Note OP wrote "I need the ID for the row".
It is possible he just needs a single ID using "TOP 1", and the error he gets is "query returned two or more values"...

Just a thought.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 06:26:58

Also in your first attempt you searched for Col2 and in your second attempt you searched for Col1.

E 12°55'05.25"
N 56°04'39.16"

Bellus
Starting Member

29 Posts

Posted - 2007-10-26 : 06:36:24

Sorry, forget about col1 or col2 :)

I can just call it col..

its an integer, so when I trie '', i just get that its wrong input for an integer...

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-26 : 06:40:29

can i just query what you tried....

quote:

Posted - 10/26/2007 : 06:04:41
--------------------------------------------------------------------------------
Its empty because when I tried

select ..... where col1=NULL,

I didnt get an error, but just 0 rows

that should be col is null

Em

Bellus
Starting Member

29 Posts

Posted - 2007-10-26 : 07:03:44

Yes,thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-26 : 07:25:52