CODEWITHSUNDEEP
sqlsql-serveralphanumericpatindex
CoolBreeze-Dev
CoolBreeze-Dev

Reputation: 1

Remove non-numeric characters from string and Keep zeros in start of the string

Keep zeros at the start of the string and remove all non-numeric.

I need to remove non-numeric characters from a string where non-numeric characters can be at any place of the string so would need to use a function.

I found this method which works for all test cases EXCEPT that it removes 0's (zeroes) from the start of the string.

create function ClearNonNumericCharacters
    (@str nvarchar(max))
returns nvarchar(max)
as
begin
    while patindex('%[^0-9]%', @str) > 0
        set @str = stuff(@str, patindex('%[^0-9]%', @str), 1, '')
    return @str
end

--Test case for 0's at the start of the string. 

declare @strTest VARCHAR(15) = null
declare @strTestNumeric INT = null

select @strTest = '00098AB$709.89'
select @strTestNumeric = dbo.[ClearNonNumericCharacters](@strTest)

select @strTestNumeric

I tried multiple functions to remove non-numeric and works for all test cases except that zeros are removed from start. I need to figure out how to keep zeros at start and only remove non-numeric from the string.

Upvotes: 0

Views: 214

Answers (0)

Related Questions