1

I need to satisfy a condition in a string that has "ns[0-9]:" where [0-9] can be any number even greater than 10.

Example:

DECLARE @test TABLE ( value VARCHAR(20))

INSERT INTO @test VALUES
( 'ns1:'),
( 'NOT OK'),
( 'ns7:'),
( 'ns8:'),
( 'ns9:'),
( 'ns10:'), 
( 'ns11:' )


SELECT *, PATINDEX( '%ns[0-9]:%', value ) passes
FROM @test

This only works on 1 to 9, not on 10 and above. I can use [0-9][0-9] but then it only works on 10 and above. I don't want a wild card between the number and the colon either.

I only want the following format to return a 1 with patindex

ns1:, ns2:, ns10:, ns11:, etc.

I also need a non-function solution. For performance reasons I want to use the string like functionality

Thanks

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Michael Pearson
  • 31
  • 3

1 Answers1

3

You can use:

select (case when value like 'ns[0-9]%:' and
                  value not like 'ns[0-9]%[^0-9]%:'
             then 1 else 0
        end) as passes_flag
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    As the OP's attempt has `':%'` I would be interested to know if there can ever be anything after the `':'` and still have a pass. In which case `'ns99:this:interests:me'` – MatBailie Sep 14 '18 at 20:58
  • I am hoping to use PATINDEX. There will be values before and after the 'ns1:' string – Michael Pearson Sep 17 '18 at 13:20
  • While your solution could solve some cases, my actual use of the PATINDEX function is required. I have a complex string where 'ns1:' is inside a huge XML string and I need to dynamically tell where the 'ns1:' portion is. – Michael Pearson Sep 17 '18 at 13:35
  • @MichaelPearson . . . It is only possible to answer the question that you actually ask. If you have another question, then consider asking it as a *new* question. – Gordon Linoff Sep 17 '18 at 13:43