How to fetch number from string in sql
Sql function to get number from string
Timecodes:
[Music]
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
pat
index that is pattern index and staff 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 pat index
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
underscore
extract numbers and udf underscore
extract alphabets
the input for both these functions is
the same
alphanumeric column that contains both
alphabets and letters
id name column from our test table
and as the name implies this function
udf underscore extract numbers
extracts numbers from the alphanumeric
column
and returns them as part of this id
column
and this function extract alphabets
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
path index and stuff functions first
let's take a look at extract numbers
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
pat index and stuff obviously we are
also using
the while loop now take a look at this
pat index 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 pat index
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 to 9 within
brackets specifies that
we want to look for a number within this
input string but then we are also using
this little carrot 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 one so
when we execute this query
we should get the value one 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 zero so this means
if this specified pattern is not found
in this input string then this path
index function
returns zero that's the reason we are
checking here
if the index is greater than zero 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 two
because that's where we have letter b so
we want to replace
this letter b that's why we specify its
position here
two 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 one two c
three 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 three
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
one two three as the result so
basically the idea is both these
built-in functions that is patent x
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 extract alphabets 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
extract numbers and extract alphabets
when we
execute this query we have the result as
expected
that's it in this video thank you for
listening
you
Related queries:
how to extract number from string in oracle sql
how to extract numbers from alphanumeric string in sql
how to get integer value from string in sql server
how to fetch substring from string in sql
how to extract date from string in sql
how to get the number from string in sql
is fetch available in mysql
query to fetch top 5 records
