don't want to retrieve 'unity'

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

don't want to retrieve 'unity'

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-09-19 : 08:49:09

declare @TempAddressParsingTable table(
id_voter int null,
id_town varchar(10) null,
full_address varchar(200) null,
ad_num int null,
ad_str1 varchar(100) null,
ad_num_suffix_a varchar (10) null,
ad_num_suffix_b varchar (10) null,
ad_unit varchar(100) null)

insert @TempAddressParsingTable (ad_str1)
select 'apple UNITY RD' union all
select 'watermelon UNITY RD unit#32' union all
select 'currency UNITY RD unit# 99' union all
select 'marrakesh UNITY RD unit #32'

select * from @TempAddressParsingTable where ad_str1 like '%unit%'
select * from @TempAddressParsingTable where ad_str1 like '%unit%'
and ad_str1 not in (select ad_str1 from @TempAddressParsingTable where ad_str1 like '% unity %')

NULL
NULL NULL NULL apple UNITY RD NULL NULL NULL NULL
NULL NULL NULL watermelon UNITY RD unit#32 NULL NULL NULL NULL
NULL NULL NULL currency UNITY RD unit# 99 NULL NULL NULL NULL
NULL NULL NULL marrakesh UNITY RD unit #32 NULL NULL NULL

trying to write a code to display record sets where there are only "units". "apple unity rd" should not be shown.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 08:53:27

This maybe?

select * from @TempAddressParsingTable where patindex('%unit[^y]%', ad_str1) > 0

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-19 : 08:55:02

[code]select * from @TempAddressParsingTable where ad_str1 like '%unit[^y]%'[/code]

Harsh Athalye
India.
"Nothing is Impossible"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-19 : 08:55:15

or something more generic:

where ad_str1 like '%[^a-z]unit[^a-z]%'

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-19 : 09:39:30

KIS_


declare @TempAddressParsingTable table( 
ad_str1 varchar(100) null)

insert @TempAddressParsingTable (ad_str1)
select 'apple UNITY RD' union all
select 'watermelon UNITY RD unit#32' union all
select 'currency UNITY RD unit# 99' union all
select 'marrakesh UNITY RD unit #32'

SELECT ad_str1 FROM @TempAddressParsingTable
WHERE ad_str1 LIKE '%unit %'
   OR ad_str1 LIKE '%unit#%'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources