Extremely easy sql not working, removing entries

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

Extremely easy sql not working, removing entries

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-11 : 22:06:59

I'm trying to remove entries where there is nothing listed for a certain column.

go
select property, roomtype
from marriottusa

where country like '%usa%' or roomrate like '%usd%' or inputparam like '%us%'
and roomtype <> ''
and roomtype <> 'courtyard'
and roomtype <> 'marriott'
and roomtype <> 'renaissance'
and roomtype <> 'residence inn'
and roomtype <> 'springhill suites'
and roomtype <> 'towneplace suites'
and roomtype <> 'fairfield inn'
and roomtype <> 'residence inn'
and roomtype <> 'Conference Centers'
and roomtype <> 'ritz-carlton'
group by property, roomtype
order by property, roomtype

If the column roomtype has a blank, i want it not listed, and I thought the part in bold took care of that part.

However I still get random entries where there's a blank in that column. I try to copy and paste whats in it to see if there indeed is something in there like a space, but there's nothing!!!

In addition I did len(roomtype) to see how many characters they are, and they all say 0. So i dont know what else to do!

chrianth
Yak Posting Veteran

50 Posts

Posted - 2008-12-11 : 22:36:36

try this...

and ltrim(rtrim(roomtype)) <> ''

quote:
Originally posted by sqlchiq

I'm trying to remove entries where there is nothing listed for a certain column.

go
select property, roomtype
from marriottusa

where country like '%usa%' or roomrate like '%usd%' or inputparam like '%us%'
and roomtype <> ''
and roomtype <> 'courtyard'
and roomtype <> 'marriott'
and roomtype <> 'renaissance'
and roomtype <> 'residence inn'
and roomtype <> 'springhill suites'
and roomtype <> 'towneplace suites'
and roomtype <> 'fairfield inn'
and roomtype <> 'residence inn'
and roomtype <> 'Conference Centers'
and roomtype <> 'ritz-carlton'
group by property, roomtype
order by property, roomtype

If the column roomtype has a blank, i want it not listed, and I thought the part in bold took care of that part.

However I still get random entries where there's a blank in that column. I try to copy and paste whats in it to see if there indeed is something in there like a space, but there's nothing!!!

In addition I did len(roomtype) to see how many characters they are, and they all say 0. So i dont know what else to do!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 22:41:50

quote:
Originally posted by sqlchiq

I'm trying to remove entries where there is nothing listed for a certain column.

go
select property, roomtype
from marriottusa

where (country like '%usa%' or roomrate like '%usd%' or inputparam like '%us%')
and roomtype <> ''
and roomtype <> 'courtyard'
and roomtype <> 'marriott'
and roomtype <> 'renaissance'
and roomtype <> 'residence inn'
and roomtype <> 'springhill suites'
and roomtype <> 'towneplace suites'
and roomtype <> 'fairfield inn'
and roomtype <> 'residence inn'
and roomtype <> 'Conference Centers'
and roomtype <> 'ritz-carlton'
group by property, roomtype
order by property, roomtype

If the column roomtype has a blank, i want it not listed, and I thought the part in bold took care of that part.

However I still get random entries where there's a blank in that column. I try to copy and paste whats in it to see if there indeed is something in there like a space, but there's nothing!!!

In addition I did len(roomtype) to see how many characters they are, and they all say 0. So i dont know what else to do!

you missed braces i guess

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-12 : 11:08:47

quote:
Originally posted by chrianth

try this...

and ltrim(rtrim(roomtype)) <> ''

Already tried that and no luck

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-12 : 11:09:15

quote:
Originally posted by visakh16

you missed braces i guess

Not sure what you mean by I missed braces

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-12 : 11:30:42

fixed it in a roundabout fashion, this is what I did

drop table marriottlistofrooms
go
select property, roomtype
into marriottlistofrooms
from marriottusa

where country like '%usa%' or roomrate like '%usd%' or inputparam like '%us%'
and roomtype <> ''
and roomtype <> 'courtyard'
and roomtype <> 'marriott'
and roomtype <> 'renaissance'
and roomtype <> 'residence inn'
and roomtype <> 'springhill suites'
and roomtype <> 'towneplace suites'
and roomtype <> 'fairfield inn'
and roomtype <> 'residence inn'
and roomtype <> 'Conference Centers'
and roomtype <> 'ritz-carlton'
group by property, roomtype
order by property, roomtype
go

delete
from marriottlistofrooms
where roomtype <> ''
go

alter table marriottlistofrooms
add id_num numeric(10,0) identity
go

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-12-12 : 11:32:00

Highlighted in red

where (country like '%usa%' or roomrate like '%usd%' or inputparam like '%us%')
and roomtype <> ''

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 00:24:16

quote:
Originally posted by DonAtWork

Highlighted in red

where (country like '%usa%' or roomrate like '%usd%' or inputparam like '%us%')
and roomtype <> ''

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

this is what i also pointed out in beginning

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-15 : 06:24:20

quote:
Originally posted by visakh16

quote:
Originally posted by DonAtWork

Highlighted in red

where (country like '%usa%' or roomrate like '%usd%' or inputparam like '%us%')
and roomtype <> ''

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

this is what i also pointed out in beginning

It is becuase OP didnt understand your reply (you pointed out it inside quote)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources