Determine if field has multiple spaces

Question

Post reply

Determine if field has multiple spaces

Barkingdog

SSCoach

Points: 18670
More actions
October 5, 2007 at 12:58 pm

#182324

I want to ignore all records in a table where a field, say Address, contains 2 or more (not necessarily consecutive) spaces. I want to process records with Address containing 0 or 1 spaces. How would you do this in t-sql? Regardless of the number of spaces in Address I don't want to modify the Address field itself.
TIA,
Barkingdog

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

select *

from yourtable

where len(address) - len( replace(address,' ','')) <= 1

* Noel

Barkingdog SSCoach Points: 18670 More actions · Answer 2

Amazingly simple! 3 woofs and a tail wag.

I was under the impression that using "replace", even as a test, would actually cause the replacement to take place in the string being tested. I thought of your algorithm but was convinced it would not work because of my, apparently, false "impression." I couldn't and didn't do it without your help.

Thanks,

Barkingdog

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 3

When in doubt, simple tests will keep you from barking up the wrong tree or missing a perfectly good fire hydrant 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 4

Or you can use this, with space instead of backslash

http://weblogs.sqlteam.com/peterl/archive/2007/09/27/Clever-way-to-get-the-records-you-want-with-certain.aspx

N 56°04'39.16"
E 12°55'05.25"

Barkingdog SSCoach Points: 18670 More actions · Answer 5

I never met a fire hydrant I didn't like.

Woof woof!

Thanks.