like not, not not like :) - 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 2005 Forums

Transact-SQL (2005)

like not, not not like :)

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-04-23 : 19:53:29

confusing topic?

well im after a way of matching addresses, ive writtin

dbdsnz.dbo.tbl_NZPhone.address like '% '+dbaddress.dbo.tbl_sts.street_type_code_small+' %'

but that will match these:

'mina st ...'
'mick rd ...'

what about these:

'mina st, ...'
'mick st,221'

so i was thinking of saying something like:

dbdsnz.dbo.tbl_NZPhone.address like '%^[a-z]'+dbaddress.dbo.tbl_sts.street_type_code_small+'^[a-z]%'

hope this makes sense...?

but that code doesnt work...

note: dbaddress.dbo.tbl_sts.street_type_code_small contains all the street sufix (st,rd, etc.)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-23 : 20:57:17

Please clarify. I'm not following you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-23 : 21:19:48

Please post your table DDL, some sample data and the result that you want

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-23 : 21:36:55

>> confusing topic?

No ... but very confusing question. We have no clue what kind of data you have in your street_type_code_small column, or what kind of logic you are looking for.

Don't give us example code that doesn't work; give us your logic in plain english that demonstrates and clearly explains you are trying to do.

- Jeff
http://weblogs.sqlteam.com/JeffS

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-04-23 : 22:01:23

street_type_code_small column
----------------------------
RD
ST
ROAD
STREET

dbdsnz.dbo.tbl_NZPhone.address
------------------------------
'mina st, 1233'
'mina st 1233'

i want to see if street_type_code_small is in 'mina st, 1233' column

i use the above query but it matches only second one 'mina st 1233'

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-04-23 : 23:56:15

You have a space before your % sign which stops if from matching. "mina st," is not like %ST %
Remove the space or otherwise take punctuation into account

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-04-24 : 00:04:21

i dont want to remove space because it will include items such as:

'stmina' how can i take all non number or characters into account?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-24 : 08:19:25

You were close in your original post; I don't know why you didnt' just try it instead of asking us. Also, consult books on-line for ways to build LIKE patterns, it has all the info you need.

try:

where yourcolumn like '%[^a-z]' + code + '[^a-z]%'

if you need to match items that may be at the very end of the column's values (i.e., "Main St") then do something like:

where yourcolumn + ' ' like '%[^a-z]' + code + '[^a-z]%'

Note that you were close, but you need to put the ^ within the brackets [ ].

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-04-24 : 09:40:57

"... at the very end of the column's values"

... or the very beginning:


where ' ' + yourcolumn + ' ' like '%[^a-z]' + code + '[^a-z]%'

Note this will perform badly if [yourcolumn] is indexed, and thus if Jeff's "ends with" variation is sufficient then stick with that.

Kristen

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-04-25 : 18:20:02

ahhh inside the bracket, simple typo was confusing me , thanx guys...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources