PATINDEX Expression

patindex regex
patindex 0-9
patindex vs charindex
patindex in oracle
sql patindex multiple patterns
patindex special characters
patindex postgresql
sql server regex match

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.

PATINDEX (Transact-SQL), PATINDEX ( '%pattern%' , expression ). Arguments. pattern. Is a character expression that contains the sequence to be found. Wildcard  The PATINDEX() function returns the position of the first occurrence of a pattern in a string. If a pattern is not found within a string, this function returns Zero. If you pass NULL as an input_parameter, it returns NULL.

Could you try this one

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

PATINDEX and Regular Expression - MSDN, PATINDEX supports pattern matching, but only in T-SQL pattern, not regular expression; for regex you need indeed a CLR assembly to solve it. Definition and Usage The PATINDEX() function returns the position of a pattern in a string. If the pattern is not found, this function returns 0. Note: The search is case-insensitive and the first position in string is 1.

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;

SQL Server PATINDEX() Function, Note: The search is case-insensitive and the first position in string is 1. Syntax. PATINDEX(%pattern%, string). Parameter Values. Parameter, Description. %​pattern  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.

SQL Server PATINDEX Function By Practical Examples, The PATINDEX() function accepts two arguments: pattern is a character expression to be found. It can contain wildcard characters such as % and '_' in the pattern  The SQL PATINDEX is a SQL String Function used to return the starting index position of the first occurrence of a pattern in a specified expression. If the specified string not found, then it will return zero.

PATINDEX Workbench - Simple Talk, The PATINDEX function of SQL Server packs powerful magic, but it is PATINDEX uses only wildcards and hasn't the OR expression or the  PATINDEX ( '%pattern%', string-expression ) Parameters. pattern The pattern for which you are searching. This string is limited to 126 bytes for patterns with wildcards. If the leading percent wildcard is omitted, PATINDEX returns one (1) if the pattern occurs at the

PATINDEX Expression, Sometimes the trick to using pattern matching is to find bad, rather than good, patterns. To validate a string of unknown length (rather than  The PATINDEX function will search for a pattern in the SourceString, and return a bigint of where that pattern is found in the sourceString. If it’s not found, then you get 0. With this you can also provide a pattern ‘%[^0-9]%’ and it will search for characters that are not numbers.

Comments
  • Are you trying to validate a string or locate a substring that meets the requirements?
  • The % wildcards will accept any characters, hence it won't filter out "My bad!" for ending in a bang.
  • @jigga: If my title is set as "M R" with space in between. when using Ltrim(Rtrim(Title)), this is not returning "MR"
  • @user3687828 so you want all the spaces to be removed? I thought you wanted the first space to be filtered out