sql - split string in tsql from column -

i've got column in table text (string) 19.5 or 7.14 19 or 11.

i want split text in 2 columns 1 text before "." , 1 text after "." .

for entries without . in string, table must have 00 in second column. data after . has 2 chars long(padded 0).

e.g. 19.5 must give column1: 19 column2: 50 ; 11 must give column1: 11 column2: 00 can me correct tsql-code?

please try:

select      yourcol,      substring(yourcol, 0, charindex('.', yourcol+'.')) col1,      left(substring(yourcol, charindex('.', yourcol+'.')+1, 2)+'00', 2) col2  yourtable 

or

select     yourcol,     cast(yourcol int) col1,     right(parsename(yourcol,1), 2) col2 (     select          convert(numeric(18,2), yourcol) yourcol     yourtable )x 

sample:

declare @tbl table(txt nvarchar(10)) insert @tbl values ('19.5'), ('11'), ('7.14')  select      txt,      substring(txt, 0, charindex('.', txt+'.')) col1,      left(substring(txt, charindex('.', txt+'.')+1, 2)+'00', 2) col2  @tbl 

or

select     txt,     cast(txt int) col1,     right(parsename(txt,1), 2) col2 (     select          convert(numeric(18,2), txt) txt     @tbl )x