Cleanup / Foreign key

I have an address table I set up so that I can start our address campaign.
There is a field (open entry) called Business Type that I allowed the works
to enter to describe the business type. Now that the entry is complete ,
I'm in cleanup phase. Now, my question is: I know I need to make Business
Type a foreign key and select distinct to insert them into their own table,
but should I do that before I clean up the inconsistencies or after. There
are about 2000 different business types, some are minor like - Car Sales -
Used and Car Sales _ Used. I'm creating a system that allows users to
search based on business type.
Before or after, why?Before!
Why? Do not allow bad data into the database in the first place, it is a
bitch getting it out.
Jay
<programmingcodeATjards.com> wrote in message
news:uVedQl9%23HHA.320@.TK2MSFTNGP04.phx.gbl...
>I have an address table I set up so that I can start our address campaign.
>There is a field (open entry) called Business Type that I allowed the works
>to enter to describe the business type. Now that the entry is complete ,
>I'm in cleanup phase. Now, my question is: I know I need to make Business
>Type a foreign key and select distinct to insert them into their own table,
>but should I do that before I clean up the inconsistencies or after. There
>are about 2000 different business types, some are minor like - Car Sales -
>Used and Car Sales _ Used. I'm creating a system that allows users to
>search based on business type.
> Before or after, why?
>sqlsql

cidr, inet, and mac data types

I've tried searching around and haven't been able to determine if SQL Server 2005 supports cidr, inet, or mac address data types. I have a database in PostgreSQL that I'm working on moving to SQL Server 2005 due to business requirements and it uses these data types.

No. There isn't a native data for cidr. However, you can just use one of the native datatypes.

http://sqlserver2000.databases.aspfaq.com/how-should-i-store-an-ip-address-in-sql-server.html

checking for null statement in sql

Hi Everyone,

I have a query

select name, address, city from table1

I want to append address +'-' + city in this query . I want to show hyphen only if both address and city are not null. If name is null then I don't want to show hyphen. How can I get around this problem.

Any help will be appreciated.

Thanks,

Vinki

It depends on how you're displaying it -

Also - will there be mulitple records returned?

You could keep the query the way it is - but, based on what is returned, with each row, write a function to do that for you, display-wise only.

|||If you want to do it within your query then look at the CASE statement -see here for details. The idea is you do something like:

SELECT Name, CASE Address + City WHEN NULL THEN NULL ELSE Address + ' - ' + City END FROM Table1

You'll need to put in more conditions, but that's the basic syntax.

Another alternative is to look at user-defined functions.|||I'd suggest this:

SELECT Name, Address + ISNULL(Address + City,'','-') + City FROM Table1

I don't understand this part of your requirement, though:

vinki wrote:

If name is null then I don't want to show hyphen

Check Table Values in the Store Procedure...

HI

I have a problem related Store Procedure, that i am trying to extact a value from Database (Like FirstName,LastName,Email Address) through Store Procedure and Display it in the DropDownList(Like: FirstName LastName ,(xyz@.xyz.com)) , and this is working correctly.

Now i try to check the value at the same time if it is NULL value in the Database then pass EmptyString to the DropDownList Like ("" "" ,(xyz@.xyz.com))\

how i can do that in the store procedure.

Comments will be appreciated.

Use the IsNull function.

|||

You could save the results to a temp table in the stored procedure then do an update replacing all nulls with "" then just return the contents of the temp table. e.g

CREATE TABLE #tmpTable
(
field1as NVARCHAR(200),
field2as integer
)

INSERT INTO #tmpTable (field1,Field2)
SELECT * FROM SelectionTable

UPDATE #tmpTable SET field1 ="" WHERE field1 isnull

SELECT * FROM #TmpTable

DROP #tmpTable

|||

You can use the Isnull function directly in your select query like this:

select firstName , lastName ,IsNull ( email ,'' )as emailfrom <table Name>

This way you are rest assured that for whichever row the email is null, it will automatically be converted to '' ( blank string ). You can write anything like 'Not Available' in the replacement part of the isnull function.

Hope this will help.