Searching for empty fields - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Searching for empty fields

Author

Topic

mn757
Starting Member

15 Posts

Posted - 2007-07-31 : 05:22:52

Hi all,

I'm trying to bring back all fields which are empty in a query. However, the database is not using nulls, so it is literally empty fields. I can use is not null, so is there anything else I can do. I can change empty fields to nulls either for other reasons.

many thanks,

Martin N

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-31 : 05:28:14

where yourColumn = ''
wouldn't work for you?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

mn757
Starting Member

15 Posts

Posted - 2007-07-31 : 05:33:31

Hi,

Thanks for reply.
Have tried that but it doesnt return any records at all. It also defaults to = "" but I guess that shouldnt make a difference ?

thanks,

Martin N

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-31 : 05:53:24

defaults to "" ??? yes that will make a difference.
how about you show us some code.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 06:01:31

quote:
Originally posted by mn757

I can use is not null, so is there anything else I can do.

So there are NULLs in the table?

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

Kristen
Test

22859 Posts

Posted - 2007-07-31 : 06:09:09

[code]
WHERE len(MyColumn) = 0

WHERE MyColumn NOT LIKE '%[^ ]%'
[/code]
??
Kristen

mn757
Starting Member

15 Posts

Posted - 2007-07-31 : 06:12:36

Hi,

Thanks for all your help. This works perfectly. Didnt even think of doing that!

WHERE len(MyColumn) = 0

thanks,

Martin N.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 06:15:58

Look at this sample information

-- Prepare sample data
DECLARE	@t TABLE (i CHAR(6), j VARCHAR(6))

INSERT	@t
SELECT REPLICATE(' ', 3), REPLICATE(' ', 2)

-- Show information
SELECT	i,
	j,
	LEN(i) AS [Len(i)],
	LEN(j) AS [Len(j)],
	DATALENGTH(i) AS [Datalength(i)],
	DATALENGTH(j) AS [Datalength(j)],
	CASE WHEN i = '' THEN 'True' ELSE 'False' END AS [Empty string(i)],
	CASE WHEN j = '' THEN 'True' ELSE 'False' END AS [Empty string(j)],
	CASE WHEN i NOT LIKE '[^ ]' THEN 'True' ELSE 'False' END AS [Not like(i)],
	CASE WHEN j NOT LIKE '[^ ]' THEN 'True' ELSE 'False' END AS [Not like(j)],
	CASE WHEN i = ' ' THEN 'True' ELSE 'False' END AS [One space(i)],
	CASE WHEN j = ' ' THEN 'True' ELSE 'False' END AS [One space(j)]
FROM	@t

Output is


i  j  Len(i)  Len(j)  Datalength(i)  Datalength(j)  Empty string(i)  Empty string(j)  Not like(i)  Not like(j)  One space(i)  One space(j)
-  -  ------  ------  -------------  -------------  ---------------  ---------------  -----------  -----------  ------------  ------------
           0       0              6              2  True             True             True         True         True          True

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

Kristen
Test

22859 Posts

Posted - 2007-07-31 : 07:08:02

"This works perfectly"

However, not very efficient for a column that happens to be indexed

Peso: I would propose the follow mod. to your code so that the width of the columns are more easily seen, and add my NOT LIKE test:


-- Show information
SELECT	[i] = '[' + i + ']',
	[j] = '[' + j + ']',
...
	CASE WHEN i NOT LIKE '%[^ ]%' THEN 'True' ELSE 'False' END AS [NotLike(i)],
	CASE WHEN j NOT LIKE '%[^ ]%' THEN 'True' ELSE 'False' END AS [NotLike(i)]

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-31 : 07:59:10

or

1 Where COALESCE(col,'')=''
2 Where col is null or col=''

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources