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

Selasa, 28 Agustus 2012

left sql function

-- left sql server function for static position
select CatNo,LEFT(CatNo,8)NewCatNo from (
    select ('MA 30012~100')CatNo union
    select ('MB 30542~20012') union
    select ('MB 30031~321') union
    select ('CA 30035~356487') union
    select ('CB 30041~310205') union
    select ('CC 31112~333')
)v0

#Results:
CatNo              NewCatNo
CA 30035~356487    CA 30035
CB 30041~310205    CB 30041
CC 31112~333       CC 31112
MA 30012~100       MA 30012
MB 30031~321       MB 30031
MB 30542~20012     MB 30542

-- sql server left function for dinamic position
select CatNo,LEFT(CatNo,CHARINDEX('~',CatNo)-1)NewCatNo from (
    select ('MA 30012567~100')CatNo union
    select ('MB 3054231~20012') union
    select ('MB 30031~321') union
    select ('CA 300352345~356487') union
    select ('CB 30041056879~310205') union
    select ('CC 311~333')
)v0

CatNo                    NewCatNo
CA 300352345~356487      CA 300352345
CB 30041056879~310205    CB 30041056879
CC 311~333               CC 311
MA 30012567~100          MA 30012567
MB 30031~321             MB 30031
MB 3054231~20012         MB 3054231

Tidak ada komentar:

Posting Komentar