In this Tutorial, We will learn How to Find and remove Numbers from string in a column using SQL Server. Remove numeric character from a string in SQL SERVER using the PATINDEX function along with the REPLACE function.
First we need to create Table
CREATE TABLE [dbo].[EmployeeV]( ID int NOT NULL, [FirstName] Varchar(200) NOT NULL, [MiddleName] Varchar(200) NOT NULL, [LastName] Varchar(200) NOT NULL, [JobTitle] Varchar(200) NOT NULL, [Department] Varchar(200) NOT NULL, [Gender] [nchar](1) NULL )
Then Insert data into EmployeeV Table
INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (1, N'Ken12', N'J', N'Sánchez', N'Chief Executive Officer', N'Executive', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (2, N'Te3r31ri', N'Lee', N'Duffy', N'Vice President of Engineering', N'Engineering', N'F') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (3, N'Ro23be4447rto', NULL, N'Tamburello', N'Engineering Manager', N'Engineering', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (4, N'Rob4', NULL, N'Walters', N'Senior Tool Designer', N'Tool Design', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (5, N'Gail5', N'A', N'Erickson', N'Design Engineer', N'Engineering', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (6, N'Jossef666', N'H', N'Goldberg', N'Design Engineer', N'Engineering', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (7, N'45454Dylan', N'A', N'Miller', N'Research and Development Manager', N'Research and Development', N'M') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (8, N'Diane55', N'L', N'Margheim', N'Research and Development Engineer', N'Research and Development', N'F') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (9, N'Gigi554', N'N', N'Matthew', N'Research and Development Engineer', N'Research and Development', N'F') INSERT [dbo].[EmployeeV] ([ID], [FirstName], [MiddleName], [LastName], [JobTitle], [Department], [Gender]) VALUES (10, N'Mi4chae43l', NULL, N'Raheem', N'Research and Development Manager', N'Research and Development', N'M')
Remove numeric character in a string
DECLARE @FirstName varchar(100)
SET @FirstName = 'Wel123come234'
WHILE PATINDEX('%[0-9]%', @FirstName)>0
SET @FirstName = REPLACE(@FirstName, SUBSTRING(@FirstName, PATINDEX('%[0-9]%', @FirstName),1),'')
SELECT @FirstName
Also Check Previous Article How to Count Male and Female Without Case Statement
- PATINDEX Function- It finds any numeric characters in the string. It returns the starting position of the first occurrence of a pattern in a specified expression.
- PATINDEX function return a value greater than o it means that a numeric character was found in the string.
- REPLACE Function – It replace that character with an empty string.
- SUBSTRING Function – It is used to extract the numeric character that was found using the stating position returned by PAINDEX.
Remove numeric character in a string using Function
CREATE Function RemoveNumericCharacterinstring(@inputstr VARCHAR(100))
Returns VARCHAR(max)
AS
BEGIN
WHILE PATINDEX('%[0-9]%', @inputstr)>0
SET @inputstr = REPLACE(@inputstr, SUBSTRING(@inputstr, PATINDEX('%[0-9]%', @inputstr),1),'')
return @inputstr;
END
Test SQL Function
SELECT [dbo].[RemoveNumericCharacterinstring]('Welco234me')
Thank you for writing this article. I appreciate the subject too.