How to remove lowercase letters from varchar

Question

I want to remove the lowercase letters from a varchar in a select. Is it possible to do it without having to implement a function?

Eg:

 declare @s varchar(max) = 'ThisIsADifferentTest'
 select /*<some opperation with the varchar>*/@s

I want to obtain 'TIADT'

HINT: it would do if we could perform a boolean intersection between varchars:

select intersection(@s,UPPER(@s))

Just ASCII `a-z` or including accented / non English characters too? — Martin Smith, Feb 19 '14 at 14:17
Do you mean lowercase? Small Caps usually refers to `Cap`ital letters that are at a `Small`er size than normal. — Damien_The_Unbeliever, Feb 19 '14 at 14:25

score 3 · Accepted Answer · edited May 23 '17 at 12:08

Probably the best performing way would be to use CLR and regex. But you can use an auxiliary numbers table.

I use spt_values below for demo purposes. You should create a permanent one.

DECLARE @s VARCHAR(2047) = 'ThisIsADifferentTest';

WITH Nums
     AS (SELECT number
         FROM   master..spt_values
         WHERE  type = 'P'
                AND number BETWEEN 1 AND 2047)
SELECT CAST((SELECT SUBSTRING(@s, number, 1)
             FROM   Nums
             WHERE  number <= LEN(@s)
                    AND SUBSTRING(@s, number, 1) LIKE '[A-Z]' COLLATE Latin1_General_Bin
             ORDER  BY number
             FOR XML PATH('')) AS VARCHAR(2047))

score 0 · Answer 2 · answered Jun 03 '22 at 14:51

0

For SQL Server 2017+

declare @s varchar(max) = 'ThisIsADifferentTest'
 
SELECT REPLACE(TRANSLATE (@s, 'abcdefghijklmnopqrstuvwxyz-_+=!@#$%^&*()' COLLATE Latin1_General_Bin, REPLICATE(' ', 40)), ' ', '')

answered Jun 03 '22 at 14:51

Graham

609
6
9

How to remove lowercase letters from varchar

2 Answers2