TSQL - Locating first occurrence of value in a string

Question

I need to run a simple select statement for a column called AddrZip to show all records that contain '1/2 ' after the first space in the column. In Access 2007 it would be:
**Left([Names],InStr(1,[Names]," ")-1), but can't find out how to do it in SQL 2005. All help will be appreciated.

Answer 1

Try this -

select * from table where addrZip like '%\ 1/2%' escape '\'

Answer 2

First, look for records with a ' ':

CHARINDEX(' ', [AddrZip]) > 0

Then look for records with a '1/2' occurring after the ' '

CHARINDEX('1/2', [AddrZip], CHARINDEX(' ', [AddrZip])) > 0

SELECT  *
FROM    ( SELECT    *
          FROM      [Addresses]
          WHERE     CHARINDEX(' ', [AddrZip]) > 0
        ) x
WHERE   CHARINDEX('1/2', [x].[AddrZip], CHARINDEX(' ', [AddrZip])) > 0

This "simplified" version may work:

SELECT  *
FROM    [Addresses]
WHERE   CHARINDEX(' ', [AddrZip]) > 0
        AND CHARINDEX('1/2', [x].[AddrZip], CHARINDEX(' ', [AddrZip])) > 0

If you want to find occurrences of '1/2' that are immediately preceded by a ' ' where the ' ' is the very first space in the string, then use the following code:

SELECT  *
FROM    [Addresses]
WHERE   CHARINDEX(' ', [AddrZip]) > 0
        AND CHARINDEX(' ', [AddrZip]) = CHARINDEX(' 1/2', [x].[AddrZip])

---

Avoid LIKE operators if at all possible. They are notoriously slow.