Help with a SQL string! 1

[tjtindal]

Here's my string:

SELECT * FROM OwnerHistory WHERE theAsset LIKE '%Benton Equipment BEC (Equipment) [CIT-1]%' ORDER BY theAsset, theDate

Everytime it's hits "Benton Equipment BEC (Equipment) [CIT-1]", it says "Invalid Pattern String" as it's error message. I'm not using apostrophes (') or anything like that, and it's going through the first 5000 entries just fine, it's just that when it hits THIS particular item, it gives me an error. Any thoughts?

 

[Sheco]

This is just a wild guess but sometimes if you have a field with a name that is a keyword like desc or user or date or whatever then you have to put it in braces like that to keep the database from being confused...

ie: SELECT [date] FROM TheTable WHERE [date] < '1/1/2006'

 

[Sheco]

... so maybe the SQL parser is confused by the braces ?

 

[Zathras]

Try
[tt]
LIKE '%Benton Equipment BEC (Equipment) _CIT-1_%'
[/tt]

 

[gmmastros]

The like statement can take a pattern, which can be very powerful. Here is an excerpt from Books On Line.

Using Wildcard Characters as Literals
You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The table shows several examples of using the LIKE keyword and the [ ] wildcard characters.

Symbol Meaning
LIKE '5[%]' 5%
LIKE '[_]n' _n
LIKE '[a-cdf]' a, b, c, d, or f
LIKE '[-acdf]' -, a, c, d, or f
LIKE '[ [ ]' [
LIKE ']' ]
LIKE 'abc[_]d%' abc_d and abc_de
LIKE 'abc[def]' abcd, abce, and abcf

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Sheco]

Ah, so yes it is confused by the braces but for a different reason that I thought.

 

[tjtindal]

Zathras, and everyone else, thanks for your help! By adding a replacer for [ to _ and ] to _, it ran on through with no problems!

 

[gmmastros]

The underscore character will ignore ANY character in that particular position. Since your searches are relatively specific, you MAY not run in to any problems. But you might.

Ex
Benton Equipment BEC (Equipment) [CIT-1]
and
Benton Equipment BEC (Equipment) =CIT-1=

would both be returned from zathras's code.

I've never run in to this problem before, but you may want to try doubling the square brackets for a more accurate match.

LIKE '%Benton Equipment BEC (Equipment) [[CIT-1]]%'




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom