All of Query was successfully tested and running well on Microsoft SQL Server 2008

Rabu, 29 Agustus 2012

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

Tidak ada komentar:

Posting Komentar