How to get only Capital letters from given value

Question

I have a table it contains ID, Description and code columns. I need to fill code column using description column. Sample Description is "Investigations and Remedial Measures" so my code should be "IRM".

Note: Is there any words like "and/for/to/in" avoid it

Answer 1

Personally I would do this with an inline table-valued function

On SQL Server 2017 or better, or Azure SQL Database:

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

On SQL Server 2016 or older:

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

In either case:

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);

Results:

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

The OPTION (MAXRECURSION 4000) is only necessary if your strings can be longer than 100 characters.