Editing Astronauts Addresses - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Editing Astronauts Addresses

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-16 : 17:06:17

Can anyone tell me if there is an easier way to perform editing on something like addresses? Here I have typical editing with an update and case statement, however it doesn't work exactly: it doesn't remove the periods as prescribed by my U&C statement. Is there a better way to do this? Anyone? Here's the sample code:

--Editing Astronauts Addresses

DROP TABLE #Astronauts
CREATE TABLE #Astronauts
(Mission nvarchar (04) NULL,
Last_Name nvarchar (20) NULL,
First_Name nvarchar (20) NULL,
Address nvarchar (30) NULL,
City nvarchar (20) NULL,
State nvarchar (2) NULL,
Zip nvarchar (5) NULL
)
GO

INSERT #Astronauts VALUES ('1915','Quetzalcoatl','Quentin ','6615 Pluto Parkway ','Indianapolis','IN','46237')
INSERT #Astronauts VALUES ('2608','Sineca ','Saturnna','11716 Mercury Mile 7th Floor','Kansas City ','MO','64134')
INSERT #Astronauts VALUES ('4179','Toutatis ','Trajan ','77 Apollo Ave. Suite 13 ','Sanford ','FL','32773')
INSERT #Astronauts VALUES ('6318','Conkrite ','Candy ','80 N. W. 58th Street #90 ','Kalispell ','MT','59901')
GO

SELECT * FROM #Astronauts
GO

UPDATE #Astronauts
SET Address =
CASE
WHEN CHARINDEX('SUITE',UPPER(Address)) <> 0
THEN LEFT(Address,CHARINDEX('SUITE',Address) - 1)+'STE '+
RTRIM(SUBSTRING(Address,CHARINDEX('SUITE',Address)+6,LEN(Address)))
WHEN CHARINDEX('#', UPPER(Address)) <> 0
THEN LEFT(Address,CHARINDEX('#', Address) - 1)+'STE '+
RTRIM(SUBSTRING(Address,CHARINDEX('#' ,Address)+1,LEN(Address)))
WHEN CHARINDEX('.', UPPER(Address)) <> 0
THEN LEFT(Address,CHARINDEX('.', Address) - 1)+
RTRIM(SUBSTRING(Address,CHARINDEX('.' ,Address)+1,LEN(Address)))
WHEN CHARINDEX('FLOOR',UPPER(Address)) <> 0
THEN LEFT(Address,CHARINDEX('FLOOR',Address) - 1)+'FL '+
RTRIM(SUBSTRING(Address,CHARINDEX('FLOOR',Address)+6,LEN(Address)))
WHEN CHARINDEX('Parkway',UPPER(Address)) <> 0
THEN LEFT(Address,CHARINDEX('Parkway',Address) - 1)+'PKWY '+
RTRIM(SUBSTRING(Address,CHARINDEX('Parkway',Address)+8,LEN(Address)))
ELSE Address
END
GO

SELECT * FROM #Astronauts
GO

Thanks....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-16 : 19:43:24

You've had a bunch of views to your thread, however no answers yet. I suspect it's due to not providing the expected result set using the sample data. Once we have that, then we know what you want and can provide a solution.

Tara Kizer
aka tduggan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-17 : 00:29:03

Post the information as suggested here
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-17 : 07:04:17

Don't forget about the REPLACE() function.

>>it doesn't remove the periods as prescribed by my U&C statement.

??? Why the heck would this be a specification? If you need strict enforcement of address formatting, then it should all be broken out into columns (street, street type, unit, etc) and enforced by the UI. Otherwise, I don't see what the point is.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-17 : 11:07:54

Gee.....club me like a baby seal why don't ya! Sorry I asked!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-17 : 11:21:01

quote:
Originally posted by Xerxes

Gee.....club me like a baby seal why don't ya! Sorry I asked!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

I don't know, I know you've been given some good advice...

So what does the resultset suppose to look like....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-17 : 12:20:42

quote:
Originally posted by Xerxes

Gee.....club me like a baby seal why don't ya! Sorry I asked!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

I guess I apologize ... not sure what for, but sorry to offend you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-17 : 12:26:31

Xerxes,

Where's the expected result set? We need that in order to help you. Please see my first post and Brett's as well.

Tara Kizer
aka tduggan

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-17 : 13:36:53

Results should be...

Mission Last_Name First_Name Address City State Zip
------- -------------------- -------------------- ------------------------------ -------------------- ----- -----
1915 Quetzalcoatl Quentin 6615 Pluto PKWY Indianapolis IN 46237
2608 Sineca Saturnna 11716 Mercury Mile 7th FL Kansas City MO 64134
4179 Toutatis Trajan 77 Apollo Ave STE 13 Sanford FL 32773
6318 Conkrite Candy 80 N W 58th Street STE 90 Kalispell MT 59901

(4 row(s) affected)

No periods, SUITE should be STE, FLOOR should be FL, # should be STE and PARKWAY as PKWY. Now I tried REPLACE:

UPDATE [dbo].[#Astronauts]
SET ADDRESS = replace(UPPER(ADDRESS),'SUITE','STE'),
ADDRESS = replace(UPPER(ADDRESS),'#','STE'),
ADDRESS = replace(UPPER(ADDRESS),'.',' '),
ADDRESS = replace(UPPER(ADDRESS),'FLOOR','FL')
GO

.....but it doesn't want 'ADDRESS' in there more than once:

Column name 'ADDRESS' appears more than once in the result column list.

.....yet, I'd rather not have to use an UPDATE & SET tandem since they are slower than a SELECT......hmmm....any other way to do this?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-17 : 13:42:59

[code]

SELECT REPLACE(Address, '.', '')
FROM
(
SELECT
Address =
CASE
WHEN CHARINDEX('SUITE',UPPER(Address)) <> 0 THEN LEFT(Address,CHARINDEX('SUITE',Address) - 1) + 'STE '+ RTRIM(SUBSTRING(Address,CHARINDEX('SUITE',Address)+6,LEN(Address)))
WHEN CHARINDEX('#', UPPER(Address)) <> 0 THEN LEFT(Address,CHARINDEX('#', Address) - 1) +'STE '+ RTRIM(SUBSTRING(Address,CHARINDEX('#' ,Address)+1,LEN(Address)))
WHEN CHARINDEX('.', UPPER(Address)) <> 0 THEN LEFT(Address,CHARINDEX('.', Address) - 1) + RTRIM(SUBSTRING(Address,CHARINDEX('.' ,Address)+1,LEN(Address)))
WHEN CHARINDEX('FLOOR',UPPER(Address)) <> 0 THEN LEFT(Address,CHARINDEX('FLOOR',Address) - 1)+'FL ' + RTRIM(SUBSTRING(Address,CHARINDEX('FLOOR',Address)+6,LEN(Address)))
WHEN CHARINDEX('Parkway',UPPER(Address)) <> 0 THEN LEFT(Address,CHARINDEX('Parkway',Address) - 1)+'PKWY ' + RTRIM(SUBSTRING(Address,CHARINDEX('Parkway',Address)+8,LEN(Address)))
ELSE Address
END
FROM #Astronauts
) t

[/code]

Tara Kizer
aka tduggan

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-17 : 14:48:57

Tara,

If the replace() erases the need for me to do all the CHARINDEX and CONCATENATION why must I include it?

I just want to perform the necessary replaces and make them stick:

something like this applied to every record:

replace(UPPER(Address),'SUITE','STE'),
replace(UPPER(Address),'#','STE'),
replace(UPPER(Address),'.',' '),
replace(UPPER(Address),'FLOOR','FL')

should I consider a....(gulp) cursor?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-17 : 15:01:54

So the code that you just posted, does that work? You just need to nest them.

And no you don't need a cursor.

Tara Kizer
aka tduggan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-17 : 15:11:23

And what about avenue, unit, road, street, parkway, apartment, and all the other parts of an address you might encounter?

Again, I would suggest that if this stuff is important, then it should be constrained at the input stage by the UI and each section should be stored very strictly in different, clearly defined columns. Otherwise, it will never be exact -- there will always be strings that will be exceptions and not come out the way you expect. Worse, you might accidentally screw up an address by replacing legitimate characters in a street name with your replacements making it unreadable.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-17 : 15:24:37

For SQL Goddess: Yes, the code worked, but it doesn't update. I need to make those edits permanent.

For Dr. CrossJoin: OK, Doc. I understand what you mean, but I am in no position to dictate the input. So, I'm looking for ways to nail down the edits at the earliest possible juncture. Can you suggest some sort of processing sieve or code that will allow me to incur the least amount of damage?

I'd rather not do this:

SELECT REPLACE(Address, '.', '')
FROM
(
SELECT
Address =
CASE
WHEN CHARINDEX('SUITE',UPPER(Address)) <> 0 THEN LEFT(Address,CHARINDEX('SUITE',Address) - 1) + 'STE '+ RTRIM(SUBSTRING(Address,CHARINDEX('SUITE',Address)+6,LEN(Address)))
WHEN CHARINDEX('#', UPPER(Address)) <> 0 THEN LEFT(Address,CHARINDEX('#', Address) - 1) +'STE '+ RTRIM(SUBSTRING(Address,CHARINDEX('#' ,Address)+1,LEN(Address)))
WHEN CHARINDEX('.', UPPER(Address)) <> 0 THEN LEFT(Address,CHARINDEX('.', Address) - 1) + RTRIM(SUBSTRING(Address,CHARINDEX('.' ,Address)+1,LEN(Address)))
WHEN CHARINDEX('FLOOR',UPPER(Address)) <> 0 THEN LEFT(Address,CHARINDEX('FLOOR',Address) - 1)+'FL ' + RTRIM(SUBSTRING(Address,CHARINDEX('FLOOR',Address)+6,LEN(Address)))
WHEN CHARINDEX('Parkway',UPPER(Address)) <> 0 THEN LEFT(Address,CHARINDEX('Parkway',Address) - 1)+'PKWY ' + RTRIM(SUBSTRING(Address,CHARINDEX('Parkway',Address)+8,LEN(Address)))
ELSE Address
END
FROM #Astronauts
) t

Because I'm STILL slicing and dicing and gluing back together. I'd rather like to do SOMETHING like this (even though this has yet to work:

UPDATE the table with these
replace(UPPER(Address),'SUITE','STE'),
replace(UPPER(Address),'#','STE'),
replace(UPPER(Address),'.',' '),
replace(UPPER(Address),'FLOOR','FL')
FROM #Astronauts
GO

AS ALWAYS, I TRULY APPRECIATE YOUR EFFORTS TO ASSIST ME!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-11-17 : 15:45:28

How about something like:

--table holds the word and their replacements
create table dbo.replaceWord ( word varchar(100), newWord varchar(100) )

insert dbo.replaceWord 
select 'Parkway','PKWY' union all
select 'Floor','Fl' union all
select 'Suite','STE' union all
select '#',''

go

--function to replace the words found in the address
create function dbo.udfFixAstro ( @address varchar(100) )
returns varchar(100)
as
begin
	declare @newAddress varchar(100); set @newAddress= @address

	select @newAddress =  replace(@newAddress,word,newword)
	from dbo.replaceWord

	return @newAddress
end
go

--update the address using the udf
update #Astronauts 
set address = dbo.udfFixAstro(address)

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-17 : 15:54:38

Thanks, Jay!

I'll try that!

How's the snow up there in Michigan

? We just got flurries here in Indiana

....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-11-17 : 16:02:02

No snow (yet)... Just COLD!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-17 : 17:26:03

Tara already suggested this, but why wouldn't nesting the REPLACE functions work for you? I've nested them a lot deeper than this:


UPDATE [dbo].[#Astronauts] 
SET ADDRESS = 
	replace(
	replace(
	replace(
	replace(UPPER(ADDRESS),'SUITE','STE')
	,'#','STE') 
	,'.',' ')
	,'FLOOR','FL')

CODO ERGO SUM

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-17 : 17:36:28

THANK YOU, SIR....MAY I HAVE ANOTHER?!!

That'll do it, Colonel!

BTW, do you get to join the SQL Joint Chiefs when you get that star?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-18 : 00:06:10

Are you using Front End application to get these data or just importing data to table?

If it is not importing, then why dont you filter the data at your Front End application and send only VALID data to table so that you dont need this type of updation?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources