CODEWITHSUNDEEP
sql-server-2005t-sqlstring-math
Sue
Sue

Reputation: 21

TSQL - Locating first occurrence of value in a string

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.

Upvotes: 2

Views: 6437

Answers (2)

Brad
Brad

Reputation: 15577

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.

Upvotes: 1

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

Try this -

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

Upvotes: 0

Related Questions