Query question

18 Mar 2006 4:44 AM

joel

Hi All,

I have a problem with my query. I want to get the 2nd occurences of space
after the comma(,) sign. I have a query but only works if the city/area is
one word.

example: correct
SELECT CHARINDEX(' ', 'Atlanta, GA 1234', CHARINDEX(' ', 'Atlanta, GA
1234')+1)

example: wrong should be 16
SELECT CHARINDEX(' ', 'Los Angeles, CA 1234', CHARINDEX(' ', 'Los Angeles,
CA 1234')+1)

thanks in advance,
joel

18 Mar 2006 8:54 AM

Jens

Hi,

will that help, if you first reverse the word with the reverse
function, as this should be (as I understood) the last space ?

REVERSE()

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

18 Mar 2006 10:51 PM

Hugo Kornelis

On Sat, 18 Mar 2006 12:44:00 +0800, joel wrote:

Show quote

>Hi All,
>
>I have a problem with my query. I want to get the 2nd occurences of space
>after the comma(,) sign. I have a query but only works if the city/area is
>one word.
>
>example: correct
>SELECT CHARINDEX(' ', 'Atlanta, GA 1234', CHARINDEX(' ', 'Atlanta, GA
>1234')+1)
>
>example: wrong should be 16
>SELECT CHARINDEX(' ', 'Los Angeles, CA 1234', CHARINDEX(' ', 'Los Angeles,
>CA 1234')+1)
>
>thanks in advance,

Hi joel,

SELECT CHARINDEX(' ', 'Atlanta, GA 1234', CHARINDEX(', ', 'Atlanta, GA
1234')+2)

SELECT CHARINDEX(' ', 'Los Angeles, CA 1234', CHARINDEX(', ', 'Los
Angeles, CA 1234')+2)

--
Hugo Kornelis, SQL Server MVP