0

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.

CoolBreeze-Dev
  • 1
  • 1
  • Procedural code is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Aug 14 '23 at 07:20
  • 2
    Don't assign it to an `int` if you want leading zeroes preserved – Martin Smith Aug 14 '23 at 07:27
  • You don't need to be doing any rbar looping over characters, can you show some proper sample data and expected results - the value in your question is already numeric only - is "." considered non-numeric? – Stu Aug 14 '23 at 07:39
  • 1
    `@strTestNumeric INT` is a number, numbers don't have leading zeros ... If you want a specific format, keep it a string ... – derpirscher Aug 14 '23 at 08:18
  • 1
    What is this number meant to represent? If the leading numbers *are* important then it's actually likely the value isn't a "number" (like a phone number, or sort or account number). If the number should be treated as a number and the leading zeroes are purely for presentation, then storing is a string based value is wrong. Instead, you should use a numerical data type and the leading zeroes should be handled in the presentation layer's formatting rules for the value. – Thom A Aug 14 '23 at 08:23
  • These are check numbers where need to remove any non-numeric and then save as an INT. – CoolBreeze-Dev Aug 14 '23 at 08:39
  • Yes "." and any other non-numeric character needs to be removed. Any digit apart from 0-9 should be removed. – CoolBreeze-Dev Aug 14 '23 at 08:41
  • 1
    Once you convert it to *int* however as already noted above it's then an integer which does not have the concept of leading zeros. Leading values are a presentation aspect not a property of a number. – Stu Aug 14 '23 at 08:45

0 Answers0