RTrim wont cut it - 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 2008 Forums

Transact-SQL (2008)

RTrim wont cut it

Author

Topic

rjhiii
Starting Member

2 Posts

Posted - 2010-01-19 : 20:17:58

Here is the issue:

I have a field with addresses. Some have apartment numbers some do not. I want delete anything that begins with "#" or "Apt" ect. without deleting the end characters from those addresses without apartment #s.

Question is, how would I code that? Please assume that once you get to a "#" "Apt" etc. you can delete the rest of it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-19 : 20:23:37

please post same sample data and expected result

KH
[spoiler]Time is always against us[/spoiler]

rjhiii
Starting Member

2 Posts

Posted - 2010-01-19 : 20:37:08

Example Data

1000 S. ERVAY ST.
100 CRESCENT CT SUITE #155
1309 MAIN STREET APT 1510
1704 PATTERSON

Expected Result

1000 S. ERVAY ST.
100 CRESCENT CT
1309 MAIN STREET
1704 PATTERSON

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-19 : 20:50:29

[code]
declare @sample table
(
data varchar(50)
)
insert into @sample ( data )
select '1000 S. ERVAY ST.' union all
select '100 CRESCENT CT SUITE #155' union all
select '1309 MAIN STREET APT 1510' union all
select '1704 PATTERSON'

select data,
new_data,
case when charindex('APT', data) <> 0
then left(data, charindex('APT', data) - 1)
else new_data
end
from
(
select data,
case when charindex('#', data) <> 0
then left(data, charindex('#', data) - 1)
else data
end as new_data
from @sample
) s
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources