Removing Duplicate Values Within a String

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 2012 Forums

Transact-SQL (2012)

Removing Duplicate Values Within a String

Author

Topic

phyrr2
Starting Member

2 Posts

Posted - 2014-12-10 : 20:00:59

I'm having a quandary. Our automated mailer software is outputting fields with the following examples:

123 Cherry St Suite 123 Suite 555
999 Palm Rd Suite 1000 Suite 2000
5 Main St Suite 13A Suite 500B

As shown, the suite #'s can be different in length. I need to figure out how to cut off the last 'Suite #' value so that only the first 'Suite #' value remains, otherwise it causes postal/deliverability issues.

is there any way to deal with this situation to be able to clean this up in a table of records accordingly?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-10 : 20:17:12

Will it always say Suite twice when this issue occurs?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

phyrr2
Starting Member

2 Posts

Posted - 2014-12-11 : 12:54:46

Only a small percentage of the mailing file will contain these, but for this specific issue, it is always related to there being two "Suites" in the address line. I think so far I can find the double Suite Value by selecting something along the line of like '*suite *suite*'.

However, with the processing I need done, I'm not sure if it would need to be selected in a different manner for an execution to work properly. I'm beginner level with SQL with a little Python experience, so bear with me here :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-11 : 13:33:14

I am not sure whey the final -3 was needed and don't have time to troubleshoot, but this seems to work:

create table #t (address varchar (200))

insert into #t values('123 Cherry St Suite 123 Suite 555'), ('999 Palm Rd Suite 1000 Suite 2000'), ('5 Main St Suite 13A Suite 500B')

select * from #t

select left(address, charindex('Suite', address, 1)+5 + charindex('Suite', substring(address, charindex('Suite', address, 1)+5, datalength(address)), 1)-3)
from #t

drop table #t

Please note that #t is used to simulate your environment. All you need is the final select, switching the object names to your object names.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources