在SQL Server视图中根据复杂的字符串模式匹配字符串列

Question

我的任务是创建一个SQL Server 2016视图，该视图将在我的开发环境中显示不符合以下三种命名约定的当前数据库名称。

我得到的三个有效的命名约定

• "XXX_YYY_DEV"
• XXX_YYY_DEV-nnn.nnn。nnn"
• XXX_YYY_DEV-nnn.nnn.nnn。nnn"

其中XXX是至少1个字母数字字符长的可变长度字符串，YYY是另一个至少1个字母数字字符长的可变长度字符串，nnn的每次出现都是一个可变长度数值(最小值='0')，可以与任何其他nnn相同。

例子:

数据库名称

• "Dinky_cu5tomer_DEV"
• 5 aussage_orders_dev-12.3.0"
• "TExt1_text2_DEV-999.57.1.57"

符合标准，因此将被排除在视野之外。

数据库名称

• "_cu5tomer_DV"
• 5 aussage_orders_now_dev-12.3.0"
• "text1_text2_dev - 999.99 a.1.57"

不符合标准，因此将被排除在视野之外。

我试着把组成部分分开，但是数字部分真的难住了我。

这是我所得到的数字位注释掉了。

CREATE View vw_NonStandardDatabaseNames 
AS
    SELECT DISTINCT 
        X.DBRealName AS 'Non-Standard Name',*
    FROM 
        (SELECT 
             db.name AS [DBRealName],
             -- Client
             (CASE 
                 WHEN PATINDEX ('%[_]%', db.name) > 1 
                    THEN SUBSTRING(db.name,1,PATINDEX ('%[_]%', db.name)-1)
                    ELSE ''
              END) AS [Client],
             -- First Underscore
             PATINDEX ('%[_]%', db.name) AS [Pos_1], 
             (CASE 
                 WHEN PATINDEX ('%[_]%', db.name) > 0 
                    THEN '_'
                    ELSE ''
              END) AS [US_1],
             (CASE 
                 WHEN PATINDEX ('%[_]DEV%', db.name) > 0 
                    THEN '_'
                    ELSE ''
              END) AS [US_2], 
             -- Database Name
             (CASE 
                 WHEN PATINDEX ('%[_]%', db.name) <= 1 
                    THEN '' -- No Underscore 1
                 WHEN PATINDEX ('%[_]DEV%', db.name) <= 3  
                    THEN '' -- No Client
                 WHEN PATINDEX ('%[_]%', db.name) = PATINDEX  ('%[_]DEV%', db.name) 
                    THEN '' -- No first underscore only second one + System Name.
                 WHEN PATINDEX ('%[_]%', db.name)+1 = PATINDEX ('%[_]DEV%', db.name) 
                    THEN '' -- First underscore but no DB Name then second underscore + System Name.
                 WHEN PATINDEX ('%[_]%', db.name) > 1 
                      AND PATINDEX ('%[_]DEV%', db.name) > 3 
                    THEN SUBSTRING(db.name, PATINDEX ('%[_]%', db.name)+1,(PATINDEX('%[_]DEV%', db.name)-(PATINDEX ('%[_]%', db.name)+1)))
                    ELSE ''
              END) AS [DBName],
           -- Second Underscore
           PATINDEX ('%[_]DEV%', db.name) AS [Pos2], 
           -- System Name
           (CASE
              WHEN PATINDEX ('%[_]DEV%', db.name) > 0 THEN 'DEV'
              ELSE ''
            END)  AS [System_Name],
           -- System Name
           (CASE
              WHEN PATINDEX ('%[_]DEV-%', db.name) > 0 THEN PATINDEX ('%[_]DEV-%', db.name) + 4
              ELSE 0
            END)  AS [POS_3] 
            ---- Hyphen after 'DEV' System Name
         --  (CASE 
            --  WHEN db.name LIKE '%[_]DEV-%' THEN '-' 
            --  ELSE ''
            --END) AS [Hyphen]
           ---- Version Major
           --'' AS [VersionMajor],
           --'' AS [Dot1],
           ---- Version Minor
           --'' AS [VersionMinor],
           --'' AS [Dot2],
           ---- Revision 
           --'' AS [Revision],
           --'' AS [Dot3],
           ---- Build
           --'' AS [Build]
      FROM sys.databases db
      WHERE db.name NOT IN ('master','model','msdb','tempdb')
  --UNION 
        --as above but with 3 digit Version
      --UNION 
        --as above but with 4 digit version
 )  X
WHERE 
    X.DBRealName != Concat(X.Client, X.US_1, X.DBName, X.US_2, X.System_Name)    --, X.Hyphen, X.VersionMajor, X.Dot1, X.VersionMinor, X.Dot2, X.Revision, X.Dot3, X.Build

Answer 1

您可以安装CLR正则表达式函数，然后您可以使用适当的正则表达式，而不是使用PATINDEX?

https://blogs.msdn.microsoft.com/sqlclr/2005/06/29/working-with-regular-expressions/