Running a sql to find blanks or empty space in database

I am trying to run a sql to find if any of my fields have a empty or blank space.
Is there anyway we can list such rows and count
Thanks in advanceYes, you can do it.

A lot depends on how you define "empty or blank space" and which database engine you are using. Assuming that your database engine does standard SQL compares (right side string promotion), I'd suggest something like:SELECT *
FROM myTable
WHERE myColumn IS NULL OR '' = myColumn OR myColumn = ' '-PatP|||In am using sybase and part of unit testing my application,
I need to see how many blank spaces exist in db.

Especially for fields which have been defined as numeric.
Your query is good for fields defined as characters. Many times we come across db where numeric fields have blanks neither 'NULL' or '0'.

Please do let me know if you have any solution for that.|||i'm not sure how to tell you this, but "fields which have been defined as numeric" cannot have blanks or spaces in them

unless sybase is really a lot different than i thought it was|||Wouldn't that make life interesting. Maybe we could also have a stateless value. hmmmm

rules on a table

In My table i'm collecting information about our customers.In that i have fields zip and phone.So i wanted to implemement a rule that zip should be atlaest of of 5 characters and phone should be 13 characters Including '-'s(333-333-3333).How can i implement these two rules on my table.First of all, decide if you really want to add those constraints. They will automagically limit your database to dealing with data like what you'd find in the United States.

If that is an Ok thing, then I would suggest that you add constraints to your table. You could use something like:ALTER TABLE myTable
ADD CONSTRAINT XCK01myTable CHECK (5 <= Len(zip))
, ADD CONSTRAINT XCK02myTable CHECK (phone
LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')Constraints like these make me nervous, since I see them as rather arbitrary, but they are much better done as constraints than as code because you can easily change them in the database if needed, instead of having to hunt down hundreds or thousands of snippets of code!

-patP|||I'm with Pat. I think you'll end up regretting this as the users start to complain about limiting their ability to enter data. The zip-code you might get away with, though what about zip-4 extensions? I don't think enforcing a 13 character phone number is going to make you a lot of friends with the users.|||True, but you can fix this in one place (the database) with one command (DROP CONSTRAINT) if it turns out to be a problem. This is far better than coding it into an executable or a web page, at least in my opinion.

-PatP|||Oh, I agree. Table constraints are the best places to store design flaws, hands down. :p|||I don't know that it's a design flaw to insure data consistency in the data layer, even if that means adding "another" layer to the data. It's isn't so much to cover design flaws as it is to allow the data group to insure data is consistent and secure for the enterprise.|||It's not a design flaw to enforce data integrity. I'm just concerned about enforcing this particular constraint, based on past experience. I guess I'd say it is a design flaw to enforce unnecessary constraints that place arbitrary restrictions on the users. Unless there is some sort of application process that depends upon the phone number being absolutely 13 characters, then why place an artificial limit on how the database can be used? You are locking out the possibility of international numbers, extensions, etc...|||i agree with blindman

"it is a design flaw to enforce unnecessary constraints that place arbitrary restrictions on the users"

what about the guy who paid a few thousand bucks to secure the number 1-800-BEST-DBA

this guy's going to be p1ssed if you force him to put the dash where you want him to put the dash

note for those who still don't get it: there is no dash on the phone keypad|||That's not an unnecessary constraint. 1800BESTDBA isn't a phone number. It's a way to represent a phone number. You can't dial it though. You can't feed it to a dialer or use it for customer service. Anyone looking at the number is going to wonder what the idiot was thinking putting it into the database like that anyway. If you want to have a descriptor for the phone number, then have one. He can put whatever he wants in there. 1-800-BEST-DBA, 1-800-STUPID-Q...whatever.

The number is 18003334444. The display handled on the front-end is 1-800-333-4444. The descriptiong, which can be displayed or not is 1-800-STUPID-Q.|||well, that just proves my point

the number is not 18003334444

it's 18002378322 -- you could look it up!

and yes, i sure can dial 1-800-BEST-DBA

even on my rotary phone!!

and of course your very descriptive "what the idiot was thinking..." reveals an attitude that might best be set aside when dealing with people in the real world

RTF Fields

I think this has been asked before so apologies in advance. However, I stil
have not found a solution or workaround.
I need to print RTF data in a reports but cannot think of any way of doing
this, short of creating a rendering extension - which is not documented (yet).
I thought maybe SP2 would have something for this but I don't seem to be
able to get access to the beta (I've been waiting since last week for
access). Maybe SQL 2005 RS B2 has this ability? Either one of these options
would be acceptable.
Can someone tell me wether I should wait for SP2, use SQL 2005 or hand code
the report? If I go with 2005 can I still use VS.Net 2003?
Sorry, lots of questions for such a simple (?) thing.
ThanksNative RTF support is still on the wishlist for a future release.
The closest you can get today is to write your own custom assembly which
converts the RTF contents from the database field into a bitmap image (as
byte array). The image can then be used inside the report - you don't need
to write a rendering extension.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonesie" <Jonesie@.discussions.microsoft.com> wrote in message
news:6DF7872D-61FF-459D-A9C9-6DE035BCAD29@.microsoft.com...
> I think this has been asked before so apologies in advance. However, I
stil
> have not found a solution or workaround.
> I need to print RTF data in a reports but cannot think of any way of doing
> this, short of creating a rendering extension - which is not documented
(yet).
> I thought maybe SP2 would have something for this but I don't seem to be
> able to get access to the beta (I've been waiting since last week for
> access). Maybe SQL 2005 RS B2 has this ability? Either one of these
options
> would be acceptable.
> Can someone tell me wether I should wait for SP2, use SQL 2005 or hand
code
> the report? If I go with 2005 can I still use VS.Net 2003?
> Sorry, lots of questions for such a simple (?) thing.
> Thanks
>|||Thanks for the prompt reply. I thought about doing something like this but
it's not that easy unfortunately as the RTF is mixed with other not RTF data
in the same report.
I've resorted to hand coding these 2 or 3 reports and will keep the RDL
versions around until the wish list beomes a reality list.
Thanks
"Robert Bruckner [MSFT]" wrote:
> Native RTF support is still on the wishlist for a future release.
> The closest you can get today is to write your own custom assembly which
> converts the RTF contents from the database field into a bitmap image (as
> byte array). The image can then be used inside the report - you don't need
> to write a rendering extension.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jonesie" <Jonesie@.discussions.microsoft.com> wrote in message
> news:6DF7872D-61FF-459D-A9C9-6DE035BCAD29@.microsoft.com...
> > I think this has been asked before so apologies in advance. However, I
> stil
> > have not found a solution or workaround.
> >
> > I need to print RTF data in a reports but cannot think of any way of doing
> > this, short of creating a rendering extension - which is not documented
> (yet).
> >
> > I thought maybe SP2 would have something for this but I don't seem to be
> > able to get access to the beta (I've been waiting since last week for
> > access). Maybe SQL 2005 RS B2 has this ability? Either one of these
> options
> > would be acceptable.
> >
> > Can someone tell me wether I should wait for SP2, use SQL 2005 or hand
> code
> > the report? If I go with 2005 can I still use VS.Net 2003?
> >
> > Sorry, lots of questions for such a simple (?) thing.
> >
> > Thanks
> >
>
>