right in sql

--right sql server function for static position
select RIGHT(MyAnimals,10)AnimalName,LEFT(MyAnimals, CHARINDEX(' ', MyAnimals)-1)qty from (
    select ('5 chickens A')MyAnimals union
    select ('15 chickens B') union
    select ('25 chickens C') union
    select ('35 chickens D') union
    select ('45 chickens E')
)v0

#Results:
AnimalName    qty
chickens B     15
chickens C     25
chickens D     35
chickens E     45
chickens A      5

--right sql server function for dinamic position
select MyAnimals,quantity,RIGHT(MyAnimals,LEN(MyAnimals)-LEN(quantity))AnimalName from (
    select MyAnimals,LEFT(MyAnimals, CHARINDEX(' ',MyAnimals)-1)quantity from (
        select ('5 chickens')MyAnimals union
        select ('14 goats') union
        select ('7 chickens') union
        select ('10 cows') union
        select ('500 ducks')
    )v2
)v3

#Results:
MyAnimals    quantity    AnimalName
10 cows            10    cows
14 goats           14    goats
5 chickens          5    chickens
500 ducks         500    ducks
7 chickens          7    chickens

sql substring

Substring function on sql server is a function like mid on asp, to get one or more characters in the middle of a set of characters.

--substring sql server function for static position
select notes,substring(notes,11,3)qty from (
    select ('bicycle A 285 unit')notes union
    select ('bicycle B 153 unit') union
    select ('bicycle C 120 unit') union
    select ('bicycle D 569 unit') union
    select ('bicycle E 587 unit')
)v1

#Results:
notes                 qty
bicycle A 285 unit    285
bicycle B 153 unit    153
bicycle C 120 unit    120
bicycle D 569 unit    569
bicycle E 587 unit    587

--substring sql server function for dinamic position
select notes,substring(notes, CHARINDEX(' ',notes)+1,3)qty from (
    select ('bicycle 285 unit')notes union
    select ('motorcycle 153 unit') union
    select ('cabriolet 120 unit') union
    select ('motor 406 unit') union
    select ('pedicab 587 unit')
)v1 

notes               qty
bicycle 285 unit    285
cabriolet 120 unit  120
motor 406 unit      406
motorcycle 153 unit 153
pedicab 587 unit    587