How to trim/replace any letters in the value?

Question

I have few columns in my old database that have values where number and letters are combined together. This is something that I have to clean and import in the new table. The most of the values that need to be converted look like this:

40M or 85M or NR or 5NR ...

Since there wasn't any validation what user can enter in the old system there still can be values like: 40A or 3R and so on. I want to import only numeric values in my new table. So if there is any letters in the value I want to trim them. What is the best way to do that in SQL Server? I have tried this:

CASE WHEN CHARINDEX('M',hs_ptr1) <> 0 THEN 1 ELSE 0 END AS hs_ptr1

but this will only identify if one letter is in the value. If anyone can help please let me know. Thanks!

Possible duplicate of [T-SQL select query to remove non-numeric characters](https://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters) — Esteban P., Sep 18 '17 at 16:25

score 1 · Answer 1 · answered Sep 18 '17 at 16:31

you can use patindex to search for the pattern. Try this code:

Code:

CREATE TABLE #temp
(
      TXT NVARCHAR(50)
)

INSERT INTO #temp (TXT)
VALUES 
    ('40M'),
    ('85M'),
    ('NR'),
    ('5NR')

SELECT LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) 
FROM (
    SELECT subsrt = SUBSTRING(TXT, pos, LEN(TXT))
    FROM (
        SELECT TXT, pos = PATINDEX('%[0-9]%', TXT)
        FROM #temp
    ) d
) t

DROP TABLE #temp

score 1 · Answer 2 · answered Sep 18 '17 at 18:45

Here's a way without a function....

declare @table table (c varchar(256))
insert into @table
values
('40M'),
('30'),
('5NR'),
('3(-4_')

select
replace(LEFT(SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',',''), PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',','')), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',',''), PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',','')), 8000) + 'X') -1),'.','')
from @table

Bozhidar Stoyneff · Answer 3 · 2017-09-19T07:37:02.480

You go with the PATINDEX function and search for a character that is not a digit. If such an index exists, then grab everything to the left of it. Something like that:

SELECT LEFT(your_field_name, PATINDEX("%[^0-9]%", your_field_name) - 1)
FROM your_table_name

UPDATE

Well, you need to take care of any edge cases. E.g. if there isn't a non-digit data the function will return 0, thus the calculation yields -1, which, indeed, is an invalid length.

I would suggest you to leverage a Common Table Expression to calculate the index of the non-digit data and then construct an IIF expression to select the correct char data. E.g.

WITH cte AS 
(
    SELECT *, PATINDEX("%[^0-9]%", your_field_name) AS NumLength
    FROM your_table_name
)
SELECT any_other_field, IIF(NumLength = 0, 
    your_field_name, 
    LEFT(your_field_name, PATINDEX("%[^0-9]%", your_field_name) - 1)
)
FROM cte

I'm getting an error message: Invalid length parameter passed to the SUBSTRING function. — espresso_coffee, Sep 18 '17 at 17:32

How to trim/replace any letters in the value?

3 Answers3

UPDATE