| Author |
 Topic  |
|
|
gongxia649
So Suave
Azores
344 Posts |
 Posted - 08/31/2006 : 16:35:49
|
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
35007 Posts |
Posted - 08/31/2006 : 16:37:35
|
What is all of this needed for? Fixing bad data?
Tara Kizer |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 08/31/2006 : 16:44:03
|
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 |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/31/2006 : 16:51:02
|
thats why im asking if there is any way to make it shorter?
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 08/31/2006 : 17:07:48
|
How would we know how to fix it if you haven't even explained its purpose?
Tara Kizer |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 08/31/2006 : 18:34:01
|
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 |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 09/01/2006 : 08:34:26
|
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.
|
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 09/01/2006 : 09:20:20
|
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 |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 09/01/2006 : 09:41:24
|
can you help me to write the function?
|
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 09/01/2006 : 10:42:44
|
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 |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 09/01/2006 : 11:07:39
|
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" |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 09/01/2006 : 11:29:29
|
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
|
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 09/01/2006 : 11:33:22
|
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" |
|
|
| |
Topic |
|
|
|
improve update statement
