I am looking for a potentially faster way to do this check:

NOT LIKE '%[^0-9]%'

This checks to ensure all characters are numbers (see description of T-SQL pattern)

Is there a faster way to do this in Microsoft SQL Server (T-SQL)?

The full context is as part of a CASE/WHEN statement in the select part of a vary large query:

Select    DATEADD(dd, CAST(CASE WHEN a.dateDuration NOT LIKE '%[^0-9]%' THEN a.Duration ELSE 1 END AS INT), a.StartDate) AS 'ourEndDate'

In the above, a is a table alias. The column a.dateDuration is a nullable varchar column. (The real names of entities have been replaced for proprietary reasons).

Indeed, variants of this are repeated in various "UNION ALL" operators, so if it could be made faster it could speed the query considerably.

The NOT LIKE operator is presumably relatively slow.

The version of the underlying database is SQL Server 2012.

CodePudding user response：

In this context performance of LIKE / NOT LIKE operator is almost for sure not a problem. If your query is slow consider first how many rows you are returning and if you are doing full scans on tables for looking interesing rows.

Here it looks like you are only trying to format/adjust your final result - if you consider SQL is too slow here you can do this processing on application server side as this is not a part of fetching data from disk.

If this is subquery please show entire query.