SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




SQL Server 2019 Installation
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



Remove Non-Numeric Character in SQL String Expression

This T-SQL tutorial includes a user-defined SQL Server function code to clear alpha characters from an input string expression. Database programmers can use the given user-defined function in their development codes for removing characters except numbers from 0 to 9.

Here is a very basic user defined SQL function which removes unwanted alpha characters which does not represent numbers in a string expression
If database developers check the regular expression used in PatIndex SQL function, they will see that the regular expression continuously checking characters representing characters except 0 to 9.

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

remove non-numeric characters from string using SQL function

Here to use user defined SQL Server function in a sample case for Transact-SQL developers

select dbo.ClearNonNumericCharacters('19Kodyaz0 4*6/2=? QA=>9')

select phone, dbo.ClearNonNumericCharacters(phone) from PhoneNumbers

Here is the output of above sample SQL function

clearing non-numeric values in a string expression in SQL Server

This SQL function code can be used after changing the PatIndex regular expression part to remove numeric characters in SQL string expression.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums






SQL Split String Related SQL Server Tutorials

Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands
Split String Into Fixed Length Pieces in SQL
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
TSQL Character Split Function in SQL Server
Case Sensitive SQL Split Function
SQL Server 2016 Split String Function STRING_SPLIT
SQL Server String Split T-SQL CLR Function Sample
SQL Server CLR Split String Function for 2-Dimensional Array
T-SQL Split User Defined Function


Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems