Ytukyg

I'm creating a patindex:

Character 1 must be Alpha and Not space, and the remaining characters should contain any A-Z,a-z,space or '-

 patindex('^ [A-Z]%[A-Za-z '-]','Worktester')

But this doesn't seem to work

It seems PATINDEX() doesn't allow this kind of pattern matching.

Can you please advise if this is correct or any links which can help on the expression?

Thank you

Sometimes the trick to using pattern matching is to find bad, rather than good, patterns. To validate a string of unknown length (rather than extract a substring) you can search for the patterns of unacceptable characters:

declare @Samples as Table ( Sample VarChar(16) );

insert into @Samples ( Sample ) values

  ( 'Worktester' ), ( 'Foo Bar' ), ( 'Voot-Plex' ),

  ( 'BR549' ), ( ' Spaced Out' ), ( 'low down' ), ( 'Oops!' );



select Sample,

  -- Check the first character for uppercase alpha only.

  PatIndex( '[^A-Z]%', Sample collate Latin1_General_BIN ) as Part1,

  -- Check the remaining characters for any mix of alpha, space and hyphen.

  PatIndex( '_%[^-A-Za-z ]%', Sample collate Latin1_General_BIN ) as Part2,

  -- Put it together into a single status.

  case

    when PatIndex( '[^A-Z]%', Sample collate Latin1_General_BIN ) = 0 and

      PatIndex( '_%[^-A-Za-z ]%', Sample collate Latin1_General_BIN ) = 0 then 'Good'

    else 'Bad' end as Status

  from @Samples;

Note that the default collation tends to be case-insensitive. Try changing the Part1 line by removing the explicit collation.

Could you try this one

patindex('[A-Z]%[A-Za-z ''-]%','Worktester')

Using PATINDEX for pattern matching can be a pain.

Since the pattern syntax for LIKE or PATINDEX is way more limited than for Regexp.

If it's just to filter out those with other characters, you could use a LIKE instead of PATINDEX.

Since they uses the same syntax, and you don't need to know the position for this.

And it seems you just have to check if the first character is a letter, and if it doesn't contain anything other than letters, space, quotes or dash.

declare @Table table (

  ID int identity(1,1) primary key, 

  Col varchar(30) not null,

  IsValid bit

);



insert into @Table (col, isValid) values

('Foobar',1), ('Foo-''Bar''',1), ('foo bar',1),

(' Foo',0), ('F0ob4r',0), ('Foo?',0);



SELECT * 

FROM @Table

WHERE Col LIKE '[a-z]%'

  AND Col NOT LIKE '%[^a-z ''-]%';

Result: Those with isValid = 1

Or use it in a CASE

SELECT *,

(CASE 

 WHEN Col LIKE '[a-z]%' AND Col NOT LIKE '%[^a-z ''-]%' 

 THEN 1 

 ELSE 0 

 END) AS isOk

FROM @Table;