SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 improve update statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gongxia649
So Suave

Azores
344 Posts

Posted - 08/31/2006 :  16:35:49  Show Profile  Visit gongxia649's Homepage  Reply with Quote
is there anyway to improve this statement. make it short? any input will be appreciated.


update TempAddressParsingTable set ad_unit = case

when right(rtrim(ad_str1),3) like 'apt' or
right(rtrim(ad_str1),3) like 'lot'
and substring(reverse(ad_str1), 4,1) in ('', ',', '.')
then right(ad_str1,3)

when right(rtrim(ad_str1),4) like 'unit' or
right(rtrim(ad_str1),4) like 'apt%' or
right(rtrim(ad_str1),4) like 'lot%'
and substring(reverse(ad_str1), 5,1) in ('', ',', '.')
then right(ad_str1,4)

when right(rtrim(ad_str1),5) like 'unit%' or
right(rtrim(ad_str1),5) like 'apt%%' or
right(rtrim(ad_str1),5) like 'lot%%'
and substring(reverse(ad_str1), 6,1) in ('', ',', '.')
then right(ad_str1,5)

when right(rtrim(ad_str1),6) like 'unit%%' or
right(rtrim(ad_str1),6) like 'apt%%%' or
right(rtrim(ad_str1),6) like 'lot%%%'
and substring(reverse(ad_str1), 7,1) in ('', ',', '.')
then right(ad_str1,6)

when right(rtrim(ad_str1),7) like 'unit%%%' or
right(rtrim(ad_str1),7) like 'apt%%%%' or
right(rtrim(ad_str1),7) like 'lot%%%%'
and substring(reverse(ad_str1), 8,1) in ('', ',', '.')
then right(ad_str1,7)

when right(rtrim(ad_str1),8) like 'unit%%%%' or
right(rtrim(ad_str1),8) like 'apt%%%%%'
and substring(reverse(ad_str1), 9,1) in ('', ',', '.')
then right(ad_str1,8)

when right(rtrim(ad_str1),9) like 'unit%%%%%' or
right(rtrim(ad_str1),9) like 'apt%%%%%%'
and substring(reverse(ad_str1), 10,1) in ('', ',', '.')
then right(ad_str1,9)


when right(rtrim(ad_str1), 2) like '#%'
and substring(reverse(ad_str1), 3, 1) in ('', ',', '.')
then right(ad_str1, 2)
when right(rtrim(ad_str1), 3) like '#%%'
and substring(reverse(ad_str1), 4, 1) in ('', ',', '.')
then right(ad_str1, 3)
when right(rtrim(ad_str1), 4) like '#%%%'
and substring(reverse(ad_str1), 5, 1) in ('', ',', '.')
then right(ad_str1, 4)
when right(rtrim(ad_str1), 5) like '#%%%%'
and substring(reverse(ad_str1), 6, 1) in ('', ',', '.')
then right(ad_str1, 5)
else null
end



tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 08/31/2006 :  16:37:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
What is all of this needed for? Fixing bad data?

Tara Kizer
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/31/2006 :  16:44:03  Show Profile  Reply with Quote
Code like this does not make any sense, and probably will not do what you are expecting:
when right(rtrim(ad_str1),9) like 'unit%%%%%' or

You should read in SQL Server Books Online about how wildcards work with a LIKE statement.



CODO ERGO SUM
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/31/2006 :  16:51:02  Show Profile  Visit gongxia649's Homepage  Reply with Quote
thats why im asking if there is any way to make it shorter?



Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 08/31/2006 :  17:07:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
How would we know how to fix it if you haven't even explained its purpose?

Tara Kizer
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/31/2006 :  18:34:01  Show Profile  Reply with Quote
quote:
Originally posted by gongxia649

thats why im asking if there is any way to make it shorter?


Did you look at SQL Server Books Online about LIKE wildcards like I suggested?





CODO ERGO SUM
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 09/01/2006 :  07:19:05  Show Profile  Reply with Quote
Follow the first link in my sig. It has awesome information that will help us help YOU.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/01/2006 :  08:34:26  Show Profile  Visit gongxia649's Homepage  Reply with Quote
MAIN ST UNIT #20
MAIN ST UNIT #2
MAIN ST UNIT #2043
MAIN ST UNIT#2043
MAIN ST UNIT2043

I want to grab the whole string after "unit" and put it into ad_unit. But after unit, there are many variations. I have presented the variations i can think of.



Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/01/2006 :  09:20:20  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Your best bet is to create a User Defined Function that accepts the ad_line1 as a parameter and returns the unit number as a result. Within that function, you can apply your logic. then, fully test your function with all different results and if all goes well you can use that function in your update.

create function GetUnit(@AddressLine1 varchar(1000))
returns varchar(100)
as
begin
declare @Unit varchar(100)
-- your logic here
return @Unit
end


Then in your update statment you simply call the function:

Update TempAddressParsingTable
set ad_unit= dbo.GetUnit(ad_Str1)

As for the logic in your function, your best bet is to create a table of different keywords to seach for (Unit, Unit #, Apt, etc) and when one of those is found, you parse the remaining part of the string as the Unit. You don't create the table over and over, of course -- it is created once and is simply maintained as more data gives you other things to add to it.


- Jeff
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/01/2006 :  09:41:24  Show Profile  Visit gongxia649's Homepage  Reply with Quote
can you help me to write the function?



Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/01/2006 :  10:42:44  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by gongxia649

MAIN ST UNIT #20
MAIN ST UNIT #2
MAIN ST UNIT #2043
MAIN ST UNIT#2043
MAIN ST UNIT2043

I want to grab the whole string after "unit" and put it into ad_unit. But after unit, there are many variations. I have presented the variations i can think of.




It looked like your original UPDATE statement tried to handle "apt" and other words other than UNIT as well. Based on what you explained the specification to be, only the word "Unit" needs to be handled. Which is it? Also, you need to specify how to handle "#" (is it part of the unit number?), what to put if there is no unit, and what about situations like "Apt No. 2", "Unit 3-a", "Apt NL3" and so on.

Your first step before writing even a single line of code in any language is to clearly identify the situation and to come up with a clear specification that covers the situations you need to handle. Only when this is clearly defined can you come up with an algorithm, and only when you have the algorithm can you start writing code.

Sample data that covers the different possibilities is also very important when testing.

- Jeff

Edited by - jsmith8858 on 09/01/2006 10:43:51
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/01/2006 :  11:07:39  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
quote:
Originally posted by gongxia649

MAIN ST UNIT #20
MAIN ST UNIT #2
MAIN ST UNIT #2043
MAIN ST UNIT#2043
MAIN ST UNIT2043

I want to grab the whole string after "unit" and put it into ad_unit.



If this is the only thing you want, you can do this...

update TempAddressParsingTable 
set ad_unit = ltrim(right(ad_str1, len(ad_str1) - (charindex('UNIT', ad_str1)+4)+1))


Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/01/2006 :  11:29:29  Show Profile  Visit gongxia649's Homepage  Reply with Quote
harsh_athalye, your code is also grabbing what is not supposed to grab.


declare @table table(ad_str1 varchar(100), ad_unit varchar(20))
insert @table (ad_str1)
select 'MAIN ST UNIT#204' union all
select 'MAIN ST UNIT1##' union all
select 'MAIN ST apt' union all
select 'MAIN ST UNIT #204'

select * from @table

update @table
set ad_unit = ltrim(right(ad_str1, len(ad_str1) - (charindex('UNIT', ad_str1)+4)+1))

select * from @table




Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/01/2006 :  11:33:22  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
quote:
Originally posted by gongxia649

harsh_athalye, your code is also grabbing what is not supposed to grab.


declare @table table(ad_str1 varchar(100), ad_unit varchar(20))
insert @table (ad_str1)
select 'MAIN ST UNIT#204' union all
select 'MAIN ST UNIT1##' union all
select 'MAIN ST apt' union all
select 'MAIN ST UNIT #204'

select * from @table

update @table
set ad_unit = ltrim(right(ad_str1, len(ad_str1) - (charindex('UNIT', ad_str1)+4)+1))

select * from @table








I suppose by this you mean it is not working for 'MAIN ST apt', that is obvious since I expected 'Unit' keyword to be present in each row. If this word is not present, what do you want to do?

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000