Remove Numbers from String SQL Server

How to use regular expression to remove number in MS SQL Server Management Studio?

If the Number part is always 6 characters below can be used.

DECLARE @ProgrammeID VARCHAR(50) = 'Prog201604L'
SELECT STUFF(@ProgrammeID, PATINDEX( '%[0-9]%', @ProgrammeID), 6, '')

If the numbers are not fixed... to extend above

CREATE TABLE #Programme ( ProgrammeID VARCHAR(50) )

INSERT INTO #Programme
VALUES
    ('Prog201604L')
    ,('Pro2015N')
    ,('Programme2010')
    ,('Prog2016L')
    ,('Pro2N')
    ,('Prog')
    ,('2010')

SELECT ProgrammeID, 
    ISNULL(
            STUFF(ProgrammeID, 
                    PATINDEX( '%[0-9]%', ProgrammeID),  -- get number start index   
                    IIF(PATINDEX( '%[0-9][a-z]%',ProgrammeID)= 0, PATINDEX( '%[0-9]',ProgrammeID), PATINDEX( '%[0-9][a-z]%',ProgrammeID)) + 1   -- get the last number index 
                    - PATINDEX( '%[0-9]%', ProgrammeID),    -- get the number character length
                     '')
        ,ProgrammeID)           -- Where there are no numbers in the string you will get Null, replace it with actual string 
        AS [Without Numbers]
FROM #Programme

this will handle cases with varying numbers and even string without number.

Hope this helps

Remove numbers found in string column

Either you do it in the language, you embedded sqlite, or you use this SQLite code, that removes all numbers:

UPDATE table SET column = replace(column, '0', '' );
UPDATE table SET column = replace(column, '1', '' );
UPDATE table SET column = replace(column, '2', '' );
UPDATE table SET column = replace(column, '3', '' );
UPDATE table SET column = replace(column, '4', '' );
UPDATE table SET column = replace(column, '5', '' );
UPDATE table SET column = replace(column, '6', '' );
UPDATE table SET column = replace(column, '7', '' );
UPDATE table SET column = replace(column, '8', '' );
UPDATE table SET column = replace(column, '9', '' );

How to remove any trailing numbers from a string?

This solution should be a bit more efficient because it first checks to see if the string contains a number, then it checks to see if the string ends in a number.

 CREATE FUNCTION dbo.trim_ending_numbers(@columnvalue AS VARCHAR(100)) RETURNS VARCHAR(100)
    BEGIN
    --This will make the query more efficient by first checking to see if it contains any numbers at all
    IF @columnvalue NOT LIKE '%[0-9]%'
        RETURN @columnvalue

    DECLARE @counter INT
    SET @counter = LEN(@columnvalue)

    IF ISNUMERIC(SUBSTRING(@columnvalue,@counter,1)) = 0
        RETURN @columnvalue 

    WHILE ISNUMERIC(SUBSTRING(@columnvalue,@counter,1)) = 1 OR SUBSTRING(@columnvalue,@counter,1) = ' '
    BEGIN
        SET @counter = @counter -1
        IF @counter < 0
            BREAK
    END
    SET @columnvalue = SUBSTRING(@columnvalue,0,@counter+1)

    RETURN @columnvalue
    END

If you run

SELECT dbo.trim_ending_numbers('More blah 12321 123123 123132')

It will return

'More blah'

Select query to remove non-numeric characters

See this blog post on extracting numbers from strings in SQL Server. Below is a sample using a string in your example:

DECLARE @textval NVARCHAR(30)
SET @textval = 'AB ABCDE # 123'

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

SQL Server - Remove some numbers in a string contains numbers

I have a column contains string with numbers formatted as comma delimiter.

There's your problem.

The solution would be to normalize your data.

For more information, read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!

Having said that, based on your comments you can't change the database structure, your only viable choice is to do it the hard way.

First, you need to split the string stored in Column1 to a table.
Then, you need to split the string in @Str_RemoveNumbers to another table.
Then you need to aggregate the result of a query taking data from one table that doesn't exist in the other back to a comma delimited string, and store that back into Column1.

Assuming you are working with 2017 or a higher version, You can use the built in functions of string_split and string_agg so at least some of the heavy lifting is done for you.

If you are working with 2016 version, the string_split is available to you but string_agg is not, you will need to replace it with another well-known solution based on stuff and for xml. You can find an example for this here. (and in many other places.)

If you are working with an older version, you need to split your strings using older techniques - pick a string splitting function from Aaron Bertrand's Split strings the right way – or the next best way.

Having said all that, here's an 2017 version based workaround (I refuse to call this mess a solution!) for your misfortune:

First, create and populate sample table (Please save us this step in your future questions):

CREATE TABLE tblTest
(
    RecID int,
    Column1 varchar(100)
);

INSERT INTO tblTest(RecId, Column1) VALUES
(1, '1,2,3,4,5'),
(2, '6,7,8,9,10');

Then, declare and populate the variables:

DECLARE @RecId int = 1,
        @Str_RemoveNumbers Varchar(MAX) = '2, 4, 20';

Now here's the "Fun" part - using a couple of common table expressions and an update statement:

-- This cte splits the column1 value to a table
WITH CTE1 AS
(
    SELECT RecID, TRIM([Value]) As [Value]
    FROM tblTest 
    CROSS APPLY STRING_SPLIT(Column1, ',') 
    WHERE RecID = @RecId
)
-- This cte returns values from column1 that doesn't exist in @Str_RemoveNumbers
, CTE2 AS
(
SELECT RecID, STRING_AGG([Value], ',') As Col1
FROM CTE1
WHERE [Value] NOT IN(
    SELECT TRIM([Value]) 
    FROM STRING_SPLIT(@Str_RemoveNumbers, ',')
)
GROUP BY RecID
)

-- this updates the table with the results of cte2
UPDATE t
SET Column1 = Col1
FROM tblTest As t
JOIN CTE2
ON t.RecId = CTE2.RecId

Verify:

SELECT *
FROM tblTest

Results:

RecID   Column1
1       1,3,5
2       6,7,8,9,10

DB<>Fiddle

How to remove Roman letter and numeric value from column in SQL

Use the TRANSLATE function like this :

SELECT TRANSLATE(Job_title, '0123456789', '          ') AS JOB_TITLE
from my_table

You can use RTRIM to complete

Fastest way to remove non-numeric characters from a VARCHAR in SQL Server

I may misunderstand, but you've got two sets of data to remove the strings from one for current data in the database and then a new set whenever you import.

For updating the existing records, I would just use SQL, that only has to happen once.

However, SQL isn't optimized for this sort of operation, since you said you are writing an import utility, I would do those updates in the context of the import utility itself, not in SQL. This would be much better performance wise. What are you writing the utility in?

Also, I may be completely misunderstanding the process, so I apologize if off-base.

Edit:

For the initial update, if you are using SQL Server 2005, you could try a CLR function. Here's a quick one using regex. Not sure how the performance would compare, I've never used this myself except for a quick test right now.

using System;  
using System.Data;  
using System.Text.RegularExpressions;  
using System.Data.SqlClient;  
using System.Data.SqlTypes;  
using Microsoft.SqlServer.Server;  

public partial class UserDefinedFunctions  
{  
    [Microsoft.SqlServer.Server.SqlFunction]  
    public static SqlString StripNonNumeric(SqlString input)  
    {  
        Regex regEx = new Regex(@"\D");  
        return regEx.Replace(input.Value, "");  
    }  
};  

After this is deployed, to update you could just use:

UPDATE table SET phoneNumber = dbo.StripNonNumeric(phoneNumber)

Creating a SQL view and removing numbers + and () från result

I would use:

select left(name, patindex('% [(+]%', name + ' (') - 1)

Here is a db<>fiddle.

You can easily add additional splitter characters in the pattern. Or simply use ' [^-a-zA-Z] if you wanted any non-alpha character.

---

Related Topics