2

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.

Brad
  • 15,361
  • 6
  • 36
  • 57
Sue
  • 21
  • 1
  • 2

2 Answers2

1

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.

Brad
  • 15,361
  • 6
  • 36
  • 57
  • Just a thought. This matches even strings 'sss 11/2' which is not what OP wants – Sachin Shanbhag Oct 22 '10 at 17:02
  • @Sachin, why not? I see a '1/2' and a ' ' before it? What that not the OP's specifications? The OP did not say that the '1/2' needed to **immediately** follow the ' '. – Brad Oct 22 '10 at 17:03
  • Yes, you naybe right. There is no mention of 1/2 following a space immediately. I have assumed the 1/2 follows space thing. Lets see what OP really wants. ;) – Sachin Shanbhag Oct 22 '10 at 17:07
0

Try this -

select * from table where addrZip like '%\ 1/2%' escape '\'
Sachin Shanbhag
  • 54,530
  • 11
  • 89
  • 103