Please check if my answer is correct

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 2005 Forums

Transact-SQL (2005)

Please check if my answer is correct

Author

Topic

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-15 : 15:57:44

I need help for writing the following problem.

Table name is Person.Address

Field Name: AddressLine1

More information: The address is in this format(1970 Napa Ct.) for 20,000 records and we do not know that the character before the first space in the address line is a number or not for all the records.

Issue: Need to fetch, the address and the postalid only where the character before the first space or break in the AddressLine1 is a number

Hintgiven : use charindex and left.

Please help me if you can

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-15 : 16:00:49

We will only help with your homework problem if you show us what you have tried so far. We need to see that you've at least made an effort to work on it yourself.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-15 : 16:19:44

Definite homework assignment...mid term assignment/test possibly.

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-15 : 16:24:05

Answer:
Select Left( AddressLine1,CharIndex (' ',(AddressLine1))-1)as Code From Person.Address

Select AddressLine1, PostalCode,StateProvinceID

From Person.Address

Where Left (Code,len(Code))=0 or Left (Code,len (Code))>0 .

quote:
Originally posted by ganatra.neha

quote:
Originally posted by tkizer

We will only help with your homework problem if you show us what you have tried so far. We need to see that you've at least made an effort to work on it yourself.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-15 : 16:56:46

I have written the answer please anybody can help me to check whether its reight or not

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-15 : 17:48:38

Where Left (Code,len(Code))=0 or Left (Code,len (Code))>0 .

What's the purpose of this? It looks to me like the len() is always going to be 0 or > 0 (you could have written it in one compare >= 0 btw). Every single reecord will fall into your WHERE clause...so why even have it? I would use SUBSTRING and CHARINDEX instead of LEFT to get the character and then use ISNUMERIC to determine if it's a number.

The way you stated the problem:

ABC5 Somewhere Street - that would be an address that fits your criteria...the '5' is the character before the space and is a number. There may be a better more efficient way but that's where I would start.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources