如何仅从给定值获取大写字母

Question

我有一个表，它包含ID，描述和代码列。我需要使用描述列填充代码列。示例描述是"调查和补救措施"，所以我的代码应该是"IRM"。

注意：是否有任何像"and/for/to/in"这样的词避免它

Answer 1

这段代码可能会对你有所帮助。

 declare @input as varchar(1000) -- Choose the appropriate size
  declare @output as varchar(1000) -- Choose the appropriate size
 select @input = 'Investigations and Remedial Measures', @output = ''
 declare @i int
select @i = 0
while @i < len(@input)
begin
select @i = @i + 1
select @output = @output +  case when  unicode(substring(@input, @i, 1))between 65 
   and 90 then substring(@input, @i, 1)  else '' end
end
SELECT @output

Answer 2

就个人而言，我会使用内联表值函数来做到这一点

在 SQL Server 2017 或更高版本或 Azure SQL 数据库上：

CREATE OR ALTER FUNCTION dbo.ExtractUpperCase(@s nvarchar(4000))
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN 
  (
    WITH s(s) AS (SELECT 1 UNION ALL SELECT s+1 FROM s WHERE s < LEN(@s))
    SELECT TOP (3) value = STRING_AGG(SUBSTRING(@s,s,1),'') 
      WITHIN GROUP (ORDER BY s.s) 
      FROM s WHERE ASCII(SUBSTRING(@s,s,1)) BETWEEN 65 AND 90
  );
GO

在 SQL Server 2016 或更早版本上：

CREATE FUNCTION dbo.ExtractUpperCase(@s nvarchar(4000))
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN 
  (
    WITH s(s) AS (SELECT 1 UNION ALL SELECT s+1 FROM s WHERE s < LEN(@s))
    SELECT value = (SELECT TOP (3) v = SUBSTRING(@s,s,1) FROM s
      WHERE ASCII(SUBSTRING(@s,s,1)) BETWEEN 65 AND 90 
      ORDER BY s.s FOR XML PATH(''), 
      TYPE).value(N'./text()[1]',N'nvarchar(4000)')
  );
GO

无论哪种情况：

CREATE TABLE #x(id int, name nvarchar(4000));
INSERT #x(id, name) VALUES
  (1, N'Belo Horizonte Orange'),
  (2, N'São Paulo Lala'),
  (3, N'Ferraz de Vasconcelos Toranto');
SELECT id, f.value FROM #x AS x
  CROSS APPLY dbo.ExtractUpperCase(x.name) AS f
  ORDER BY id OPTION (MAXRECURSION 4000);

结果：

id     name
----   ----
1      BHO
2      SPL
3      SVT    

仅当字符串的长度超过 100 个字符时，才需要OPTION (MAXRECURSION 4000)。