SQL Server- Remove ALL letters in a String

Question

If I have a VARCHAR column that has alphanumeric values. How do I remove only the letters and leave all other characters please? This is NOT a duplicate (at least I don't think it is) since I want to leave all special characters and numbers in it's place but remove only alphabets.

    Input       Desired Result
    123         123
    123S        123
    A123,123    123,123
    a123..A123  123..123

I have tried using the following but don't get the desired output..

DECLARE @textval NVARCHAR(30)
SET @textval = 'S123..S123'

SELECT LEFT(SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000) + 'X') -1)

SELECT     STUFF(@textval, 1, PATINDEX('%[0-9]%', @textval)-1, '')

I don't think this is a duplicate as my question was to leave all special characters and numbers intact and ONLY remove alphabets. — Harry, Oct 12 '17 at 19:28

score 1 · Accepted Answer · edited Oct 12 '17 at 22:35

1

If you want to remove all Alphabets from Your String simply do through loop in your string.

DECLARE @str NVARCHAR(MAX);
DECLARE @lpcounter INT;
SET @str = 'A123..Z123';
SET @lpcounter = 0;
WHILE @lpcounter <= 26
    BEGIN
        SET @str = REPLACE(@str, CHAR(65+@lpcounter), '');
        SET @lpcounter  = @lpcounter + 1;
    END;
SELECT @str;

edited Oct 12 '17 at 22:35

CDspace

2,639
18
30
36

answered Oct 12 '17 at 05:01

Yogesh Sharma

49,870
5
26
52

Brilliant.. thank you.. this is exactly what I needed – Harry Oct 12 '17 at 19:26

SQL Server- Remove ALL letters in a String

1 Answers1