Pattern Matching Query in SQL

[Gin_bee]

I have the following query


SELECT PATINDEX('%[A-Z][A-X0-9][0-9]% [0-9][A-Z][A-Z]', LTRIM(RTRIM(Address))), Address, IDNumber
FROM <MYTABLE>

What I want to be able to do is ensure there is white space before the first part of my PATINDEX , PATINDEX('[A-Z]….

I’ve tried

PATINDEX(' [A-Z]….
PATINDEX('%[A-Z]….
PATINDEX('% [A-Z]….
PATINDEX('[ ][A-Z]….

What I want to ensure is that the PATINDEX doesn’t look in the middle of a word and find a match, which is what it is currently doing.

I'm using SQL Server 2000.

Thanks

[Gin_bee]

Managed to work around problem but would be interested to know if any of you know how to solve this issue. Suspect the problem may be to do with leading whitespace.

[Mongo]

Assuming you want this kind of white space noise ( tab, CRT, vtab, page, LF, etc.)
and those that match your lead criteria in position one, this may give you an idea.

patindex('%[' char(9) + char(10) + char(11)+ char(12)+ char(13) + char(32) 
  + char(160) + '][A-Z][A-X0-9][0-9]% [0-9][A-Z][A-Z]', ' ' + RTRIM(Address)), ...

HTH.