query to find embedded spaces - 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

General SQL Server Forums

New to SQL Server Programming

query to find embedded spaces

Author

Topic

thebrenda
Starting Member

22 Posts

Posted - 2012-05-04 : 16:40:51

I have some erroneous data in a text field. The field is city_st and it is suppose to contain values like "Winter Park FL", with only a single space between each work. But one client has records with values like "Winter Park FL". I want to identify these records. I think of it as identifying those records that have city_st with two embedded spaces. But whatever works. Is there a query that will find these records?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-05-04 : 17:12:02

SELECT *
FROM YourTable
WHERE city_st LIKE '% %'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

thebrenda
Starting Member

22 Posts

Posted - 2012-05-05 : 09:45:30

Sorry, key peice of information. The field is fixed width and padded with trailing spaces. There is nothing that i can do to change that. It is a large customer at a third party data center, I do not own the data. I am merely trying to identify erroneous city states.

Thanks

thebrenda
Starting Member

22 Posts

Posted - 2012-05-05 : 09:47:56

Want to identify the bad data fields.

Good Data
"WINTER PARK FL "
Bad Data - with multiple spaces between city and state
"WINTER PARK FL "

thebrenda
Starting Member

22 Posts

Posted - 2012-05-05 : 09:50:02

The forum keeps removing the multiple spaces in my examples. So assuming x is a space.

Want to identify the bad data fields.

Good Data
"WINTERxPARKxFLxxxxxxxxxxxxx"
Bad Data - with multiple spaces between city and state
"WINTERxPARKxxxxxxxxxxFLxxxx"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-05 : 09:55:18

You can use the [code] tags to keep spaces visible in your post.

If you want to test for existence of more than one consecutive spaces except at the end, you can make slight modification to the code Tara posted:

SELECT *
FROM YourTable
WHERE RTRIM(city_st) LIKE '%  %' -- Two spaces between % signs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources