Hey guys, I'm Venkat and in this video we'll discuss two common tasks that are usually asked
in a sql interview. Write a sql query to extract only alphabets from a given alphanumeric string.
Similarly, write a sql query to extract only numbers from a given alphanumeric string. This
database table has only one column and it contains both numbers and alphabets. Now,
the task at hand is to write a query that extract numbers and letters into separate columns as you
can see here. If you want to follow along, here is the sql script that creates the database table and
populates it with test data. I'll have the script available in the description of this video if you
need it. Now, let me execute this SELECT query so we can see the test data. Now, with this sql
interview question, the interviewer is basically checking if you know, how to write user defined
functions and how to use sql server built-in functions especially PATINDEX(), that is pattern
index and STUFF(). We discussed user defined functions in detail in our sql server tutorial
for beginners course. Please check out the videos from Parts 30 to 33. We also discussed PATINDEX()
and STUFF() functions in Part 24. I'll include the link to the course page in the description of this
video. Now, remember what we really want to do is, from a given alphanumeric string column we want
to extract numbers and alphabets into their own columns and here is the completed query for that.
First, let me execute this. We have the output as expected. In this sql query, we are using
two user defined functions UDF_ExtractNumbers() and UDF_ExtractAlphabets(). The input for both
these functions is the same alphanumeric column that contains both alphabets and letters,
"IDName" column from our TestTtable and as the name implies this function UDF_ExtractNumbers()
extracts numbers from the alphanumeric column and returns them as part of this "Id" column and this
function UDF_ExtractAlphabets(), again as the name implies extracts alphabets and returns them
as part of this "Name" column. The implementation of both these functions is pretty straightforward
if we really understand how to use built-in PATINDEX() and STUFF() functions. First, let's
take a look at UDF_ExtractNumbers() user defined function. We are creating a user defined function,
so, create function, the name of the function and to this function, we want to pass our alphanumeric
string as an input, so, we have an input parameter and it is going to return the extracted numbers
as a string and here is the implementation, pretty straightforward, we are using both these functions
PATINDEX() and STUFF(), obviously we are also using the WHILE loop. Now, take a look at this
PATINDEX() function, it has got two input parameters - the pattern that we want to
search for and that's the first parameter and the input string within which we want to look for this
pattern. Now, let me include a simple SELECT statement that explains the use of PATINDEX().
Our input string contains both alphabets and numbers and here is the pattern. To specify
the pattern, I'm using a regular expression here. Basically, with this regular expression
[0-9] specifies that we want to look for a number within this input string, but then we
are also using this little caret symbol here just before 0. That means, we want to do the opposite,
look for any non-numeric character. Basically, this is going to return us the first occurrence
of a non-numeric character from this string and if we look at this input string we have an alphabet
at position 1. So, when we execute this query, we should get the value 1. Now, let's remove this
letter "A" from this input string and then execute the same query. Notice, now we have the value 2
as the result, why? Well, that's because within this input string we have the first occurrence
of a non-numeric character, this letter "B" at position 2. Now, what do you think is going to
happen if we don't have any non-numeric characters at all within the input string? Well, let's see
it in action. Let's make a copy of this query and then include only numbers within our input string.
The result is 0. So, this means, if this specified pattern is not found in this input string,
then this PATINDEX() function returns 0. That's the reason we are checking here
if the index is greater than 0. If it is greater than zero, then within this input string we found
a non-numeric character and we want to remove that non-numeric character, for that we are
using the built-in function STUFF(). Now, let's understand this function with a simple example.
Let me paste a query here. We are using STUFF() function and the first parameter here is our
input string and within this string, we have the first occurrence of a non-numeric character at
position 2 because that's where we have letter "B". So, we want to replace this letter "B",
that's why we specify its position here 2, and we want to replace just this one letter and that
is specified by this third parameter. How many characters you want to replace? In our case,
we only want to replace this one non-numeric character and with what we want to replace it?
with an empty string. Okay, so, when we execute this, this letter "B" must be gone
from our input string, meaning the result should be "12C3". Okay, let's execute this.
There we go. Now, let's copy this result and specify this as the input string.
Now, where do we have the first occurrence of a non-numeric character within this input string?
Well, this letter "C" is present at position 3. So, here we specify that position and we
want to replace that one character with an empty string. So, when we execute this, this letter "C"
must be gone within this input string, so, we should get "123" as the result. So
basically, the idea is, both these built-in functions, that is PATINDEX(), STUFF() and
this WHILE loop are working together to remove every non-numeric character from the input string.
Now, there is no need to check if this processed input string is null, because it's never going to
be null, it will either contain only numbers or it will be an empty string. To keep this function
implementation simple, we are simply going to return the processed input string. We'll let the
calling code decide what to do if the string is empty. So, let's remove this null check,
and alter the function
Now, we have UDF_ExtractAlphabets() user defined function here and if you look at the
implementation, it's very very similar to extract numbers function, the only difference really is
the regular expression that we are using. So, with this expression we are basically saying
we want to find all non-alphabetic characters a to z, both lower and upper case and then replace
them with an empty string. So, this function is going to return us, all the alphabets in
a given alphanumeric input string and here is an example for this function usage. Let's execute it.
Finally, in this select query we're using both the user-defined functions UDF_ExtractNumbers()
and UDF_ExtractAlphabets(). When we execute this query, we have the result as expected.
That's it in this video. Thank you for listening.