Introduction

This article gives an explanation of how to split alphabets from the alphanumeric string in an SQL server. Here I'll also explain how to create a function in an SQL server.

In my previous article, I explained what is an alphanumeric string and how to split numbers from an alphanumeric string in an SQL server that you might like to read.

While working with data-driven applications, sometimes we need to split the numbers and alphabets from the input string as per the given requirement. I got many emails from the students and beginner programmers to write an article on ways to get only a varchar value from the string in the SQL server. So today in this article I'll explain how to archive this requirement to split the numbers and alphabets and return only the varchar value from the string.

Requirement

1) How to get alphabets from an alphanumeric string in SQL Server?

Implementation

So, let's create a query to split the alphabet from the string in the SQL server.

Get the Alphabet from the string

Let's split the alphanumeric string and get only alphabets from the line. So, we will take an example for demonstration. 

I have my enrollment number, which is a combination of numbers and alphabets, and I want only alphabets from my enrollment number.

Example 

Input (Enrollment Number): SOE14CE13017

Expected Output: SOECE

SQL Query to Get Alphabets From String

DECLARE @strEnrollmentNumber NVARCHAR(MAX) = 'SOE14CE13017'
DECLARE @intNumber INT  
 
SET @intNumber = PATINDEX('%[^A-Za-z]%', @strEnrollmentNumber)  
 
WHILE @intNumber > 0  
  BEGIN
	SET @strEnrollmentNumber = STUFF(@strEnrollmentNumber, @intNumber, 1, '' )  
	SET @intNumber = PATINDEX('%[^A-Za-z]%', @strEnrollmentNumber )  
END

Explanation

As you can see in the query above, here, we have declared two different temp variables @strEnrollmentNumber which indicates an Input string, and @intNumber which is taken to check whether the input string contains a number or not. Then using the PATINDEX function of the SQL server we identified whether the string input string contains a number or not and stored the return value of this function into @intNumber. 

In an SQL server, PATINDEX is a function that accepts a search pattern and expression(input string) as a parameter and returns, starting position of the first occurrence of the pattern in a specified expression(input string), PATINDEX will return 0 if the pattern is not found in the specified expression(input string). Here, we have used a pattern '%[^A-Za-z]%' that indicates only alphabets from a to z and A to Z.

Now, by using the while loop in the SQL server we removed the numbers from the input string which not match with the given pattern '%[^A-Za-z]%' one by one using the STUFF function and store the result in the @strEnrollmentNumber variable and again set the value of @intNumber as per the specified pattern '%[^A-Za-z]%' as we used condition @intNumber > 0  in while loop, So it will do the same process again and again and remove numbers from the input string one by one till @intNumber gets 0 and remove all the numbers from the input string.

In SQL Server STUFF() function is used to delete a specified sequence of characters from a source/Input string and then insert another set of sequences of characters at a specified starting point. I have written an article on the STUFF() function with syntax and examples that you might like to read.

Use of Query

SELECT @strEnrollmentNumber

Output

SOECE

You also can create a function to get only alphabets from the input string to reduce the complexity of the query.

Function to Get Alphabets From String

CREATE FUNCTION [dbo].[GetAlphabetsFromString]
(
	@strInputString  VARCHAR(MAX)
)  
RETURNS VARCHAR(MAX)
AS  
BEGIN  
	DECLARE @intValue INT  
	SET @intValue = PATINDEX('%[^A-Za-z]%', @strInputString)  
BEGIN  
	WHILE @intValue > 0  
	BEGIN  
		SET @strInputString = STUFF(@strInputString, @intValue, 1, '' )  
		SET @intValue = PATINDEX('%[^A-Za-z]%', @strInputString )  
	END  
END  
	RETURN ISNULL(@strInputString,'')  
END  
GO

Use of Function

SELECT dbo.GetAlphabetsFromString('SOE14CE13017')

Output

SOECE

Explanation

As you can see in the created function above here we have created a function that accepts inputString as an argument and all the logic is the same as I explained in the above SQL query to return only alphabets from the string. Finally, this function returns the varchar value from the string and If the input string dose does not contain any alphabets then this function will return an empty string.

Summary

In this article, we learned how to split the alphabets from the alphanumeric string in the SQL server as well as about the PATINDEX() and STUFF() functions of the SQL server and way to create a function in SQL server that returns a varchar value.

Tags:

how to split alphanumeric string in sql

sql query to find alphabets in a column

how to split string with space in sql

split string in sql with delimiter

sql count alpha characters in string

split string function in sql server