improve update statement - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

improve update statement

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-08-31 : 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

38200 Posts

Posted - 2006-08-31 : 16:37:35

What is all of this needed for? Fixing bad data?

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-31 : 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

344 Posts

Posted - 2006-08-31 : 16:51:02

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-31 : 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)

7020 Posts

Posted - 2006-08-31 : 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
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-01 : 07:19:05

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

gongxia649
So Suave

344 Posts

Posted - 2006-09-01 : 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

7423 Posts

Posted - 2006-09-01 : 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

344 Posts

Posted - 2006-09-01 : 09:41:24

can you help me to write the function?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-01 : 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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-01 : 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

344 Posts

Posted - 2006-09-01 : 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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-01 : 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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources