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

Rabu, 29 Agustus 2012

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

Tidak ada komentar:

Posting Komentar