1

I have a table called Person, and a NVarChar column called Notes.

The Notes column has a lot of text in it, but always begins with a number of some kind, with /'s inserted throughout.

For example:

1/23 some text
45/678/9%*&^%$#@
02/468/ some other text

I need to select the first character position that isn't a digit or /.
I don't care whether the position is 0-based or 1-based; I can accommodate that after the fact.

In this example, if I'm using 1-based character positions, the selection should produce the following:

5
9
8
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Giffyguy
  • 20,378
  • 34
  • 97
  • 168

1 Answers1

6

So you're looking for an index that matches some sort of pattern, say a pattern index. If we're whimsical, we might abbreviate it to PATINDEX. 

SELECT PATINDEX('%[^0-9/]%', Notes)
FROM Person
Jeroen Mostert
  • 27,176
  • 2
  • 52
  • 85
  • Oh cool, I've never seen that keyword before. Regarding the syntax, if I wanted to add `-` to the the pattern as well, in addition to the `/`, how would I notate that? – Giffyguy Jun 06 '17 at 15:34
  • 2
    Tack it on at the start or the end: `[^0-9/-]`. The `-` is only special if it could form some valid range between two other characters. Some more examples [here](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/wildcard-character-s-to-match-transact-sql), though the documentation is sparse. – Jeroen Mostert Jun 06 '17 at 15:37