How to substring non constant strings?

I have the following query:

UPDATE AmmUser.ORDINI_SCANSIONATI
SET NUMERO_ORDINE=replace((substring([Text],PATINDEX('%NR. [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',[Text]),13)),'NR. ',''),
    STATO=1
from AmmUser.ORDINI_SCANSIONATIIndexes
inner join   AmmUser.ORDINI_SCANSIONATIDocs
on ORDINI_SCANSIONATIIndexes.DocumentID=ORDINI_SCANSIONATIDocs.DocumentID
inner join AmmUser.ORDINI_SCANSIONATI
on ORDINI_SCANSIONATIDocs.RecordID =ORDINI_SCANSIONATI.DsRecordID
where PageNumber IS NULL AND STATO IS NULL

With this query I try to associate the value of substring to my variable NUMERO_ORDINE when I find the “NR.” string in the Text column.

Sometimes I don’t have the “NR.” string, so this query doesn’t work and I get wrong values from substring function.

How can I create the same substring when I don’t have the “NR.” string into Text? I always need to isolate nine numbers.

Answer

One method is to use a CASE expression:

(case when [Text] like '%NR. [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
      then replace(substring([Text],
                             PATINDEX('%NR. [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', [Text]),
                             13
                            ), 'NR. ', ''
                  )
 end),
STATO = 1

You can also add the pattern to the end of the string when it is being searched:

replace(substring([Text],
                  PATINDEX('%NR. [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',
                           [Text] + 'NR. 000000000'),
                  13
                 ), 'NR. ', ''
        ),
STATO = 1

This is a little tricky in this case because the pattern is a little complicated.