Angular 12 CRUD Example
In this article, we will learn angular 12 CRUD example with web API as well as way to implement cascading dropdown, searching, sorting, and pagination...
May 30, 2021 read moreIn this article, we will learn angular 12 CRUD example with web API as well as way to implement cascading dropdown, searching, sorting, and pagination...
May 30, 2021 read moreIn this article i am going to explain how you can use angular js table with bootstrap 4 in asp.net web form, and also show you how you can display records...
December 08, 2018 read moreCodingvila also allowing a guest post for digital marketing, where you can explore your business, product, or services in terms of articles. write quality.......
August, 21, 2021 read moreIn this article, we will learn how to create a bar chart in angular 12 using ng2-charts. Here, I'll explain how to create an angular 12 project in visual........
May 29, 2021 read moreThis article gives an explanation about convert Datatable to CSV in c# and explains the efficient way to write CSV files from Datatable as well as show...
March 01, 2020 read moreThis article provides an explanation about how to merge multiple pdf files into single pdf in using Itextsharp in c# here I also explained the use of Itextsharp.
January 22, 2019 read moreThis article gives an explanation about 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 we working with any 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 an only varchar values from the string in SQL server. So today in this article I'll explain how to archive this requirement to split the numbers and alphabets and return only varchar value from the string.
1) How to get alphabets from an alphanumeric string in SQL Server?
So, lets create a query to split the alphabets from the string in SQL server.
Let's split the alphanumeric string and get only alphabets from the string. 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
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 that is taken to check whether the input string contains a number or not. Then using the PATINDEX function of the SQL server we have identified that the string input string contains a number or not and stored the return value of this function into @intNumber.
In SQL server PATINDEX is a function that accepts 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 deletes a specified sequence of characters from a source/Input string and then inserts another set of sequence of characters at a specified starting point. I have written an article on 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.
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.
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() function of the SQL server and way create a function in SQL server that returns a varchar value.
Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.
Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.
Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.
Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.
If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com
Tutup Konverter!sentiment_satisfied Emoticon