CHARINDEX Issue - 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)

CHARINDEX Issue

Author

Topic

JDrown
Starting Member

10 Posts

Posted - 2008-03-04 : 08:41:37

I have used CHARINDEX successfully to search for a substring going from left to right in a string, but how do I search from the end of a string and go backwards?

Here is a sample of the problem I am trying to solve. Here is a list of cities, some with two names and some with more than two. I need a way to extract only those cities with two names. I wanted to do this by locating the first space in the string and the last space in the string. If the first space equals the last space, then the city should only be a two word city. Otherwise it is a three name city or greater. So what string functions can I use to locate the first and subsequent spaces in a string?

Here is the current version of my SQL statement:

SELECT
City.City,
CHARINDEX (' ' ,City.City) AS First_Space
FROM
City
WHERE
CHARINDEX (' ' ,City.City) <>0

AND HERE IS A SMALL SAMPLE OF WHAT IS RETURNED:

MARINE CITY 7
MARINE ON SAINT CROIX 7
MARION CENTER 7
MARION HEIGHTS 7
HIGH RIDGE 5
HIGH ROLLS MOUNTAIN PARK 5
HIGH SHOALS 5

Thanks to all who understand and can offer usable advice in my dilemma.

Jason.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-04 : 08:56:27

Something like this?

declare @t table
(
	a varchar(100)
)

insert into @t
select 'MARINE CITY 7' union all
select 'MARINE ON SAINT CROIX 7' union all
select 'MARION CENTER 7' union all
select 'MARION HEIGHTS 7' union all
select 'HIGH RIDGE 5' union all
select 'HIGH ROLLS MOUNTAIN PARK 5' union all
select 'HIGH SHOALS 5'

select * from @t
where parsename(replace(a, ' ', '.'), 2) is not null

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-04 : 10:10:57

or

select * from @t
where len(a)-len(replace(a, ' ', ''))<3

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 10:56:04

I think I prefer

SELECT	*
FROM	@t
WHERE	a NOT LIKE '% % % %'

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-04 : 11:08:07

quote:
Originally posted by Peso

I think I prefer
SELECT *
FROM @t
WHERE a NOT LIKE '% % % %'

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

I liked that!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources